Technocrat

  • Home
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Sunday, March 8, 2015

SQL Syntax

 Sumit Kar     March 08, 2015     Programming, SQL     No comments   


Some of the most important SQL commands are listed below. These below SQL Syntaxes will be suitable for quick reference.



SELECT [hint][DISTINCT] select_list

FROM table_list



[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]



[ORDER BY order_list [ASC DESC] ]

[FOR UPDATE for_update_options]





SQL Select Statement



SELECT "column name" FROM "table name"



Example:

Select salary from emp;



SQL Where



SELECT "column name"

FROM "table name"

WHERE "condition"



Example:

Select salary from emp

Where salary > 2000



SQL Distinct



SELECT DISTINCT "column name"

FROM "table name"



Example:

Select DISTINCT name from emp;



SQL And/Or



SELECT "column name"

FROM "table name"

WHERE "condition"

{[ANDOR] "condition"}+



Example:

SELECT salary

FROM emp

WHERE Salary > 1000

OR (Salary <> 275)



SQL Between



SELECT "column name"

FROM "table name"

WHERE "column name" BETWEEN 'value1' AND 'value2'



Example:

SELECT *

FROM emp

WHERE Date BETWEEN 'Jan-01-1999' AND 'Jan-15-1999'



SQL In



SELECT "column name"

FROM "table name"

WHERE "column name" IN ('value1', ‘value2’ ...)



Example:

SELECT *

FROM emp

WHERE last_name IN ('sharma', 'dhall')



SQL Like



SELECT "column name"

FROM "table name"

WHERE "column name" LIKE {PATTERN}



Example:

SELECT *

FROM emp

WHERE last_name LIKE '%EN%'



SQL Order By



SELECT "column name"

FROM "table name"

[WHERE "condition"]

ORDER BY "column name" [ASC, DESC]



Example:

SELECT name, Salary

FROM emp

ORDER BY name DESC



SQL Count



SELECT COUNT ("column name")

FROM "table name"



Example:

SELECT COUNT (salary)

FROM emp



SELECT * FROM Table;

In the above query, SELECT and FROM are SQL keywords, * is a wildcard which means “all columns”



Commonly available SQL keywords related to SELECT include:




  1. FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other.

  2. WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY.

  3. GROUP BY is used to combine rows with related values into elements of a smaller set of rows.

  4. HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved. HAVING acts much like a WHERE, but it operates on the results of the GROUP BY and hence can use aggregate functions.

  5. ORDER BY is used to identify which columns are used to sort the resulting data.





Returning a Single Column from a table






The syntax is as follows:


SELECT Column FROM Table;





Oracle uses the ANSI standard concatenation operator, . Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:





SELECT FirstName ' ' LastName AS Name FROM EMP_Database;





Sorting with the ORDER BY Clause





The ORDER BY clause is always applied after all other clauses are applied, such as the WHERE and GROUP BY clauses. Without an ORDER BY clause in an SQL statement, rows will often be retrieved in the physical order in which they were added to the table. The default behavior is to sort rows in ascending order.





SELECT ColumnA, ColumnB FROM Table


ORDER BY ColumnA ASC;





Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a better idea to include it to ensure that your queries are as easy to read as possible.





Also, you may want to sort your rows in descending order, in which case you use the DESC keyword:





SELECT ColumnA, ColumnB FROM Table


ORDER BY ColumnA DESC;





Filtering Data





DISTINCT always retrieves the first value from a repeating group. If there are multiple repeating groups DISTINCT will retrieve the first row from each group.





Hence, DISTINCT will always require a sort. DISTINCT can operate on a single or multiple columns.





SELECT DISTINCT ColumnA FROM Table;





In order to filter out duplicate rows, you use the DISTINCT keyword.





If you want to retrieve multiple columns, you can guarantee that every row you obtain contains unique data for the specified set of columns.


For example, the following query will return only unique combinations of customer names and debit card details:





SELECT DISTINCT CustomerName, DebitCard FROM Customers;





This doesn't mean that you won't have duplicates in either column, only that each combination is unique. You could, for example, have several customers with the same name but with different debit card numbers.





Using WHERE Clause





We can use the WHERE clause to restrict the rows returned by a query.





SELECT ColumnA, ColumnB, ColumnC FROM Table


WHERE Condition;





Condition is very flexible, allowing you to test for equalities and inequalities in column data, ranges of values to look for etc. You achieve all this using a simple syntax that includes various operators and keywords that, when combined, allow you to search for pretty much anything.





We can use following comparison operators with the WHERE clause:














OperatorMeaning
=Equal
<>, !=, ^=Not Equal
>Greater Than
<Less Than
>=Greater Than Or Equal
<=Less Than Or Equal




And many more for example BETWEEN ...AND..., LIKE, IS NULL etc.





Some examples of where





1)


SELECT name FROM bedroom


WHERE bedcolor = 'BLACK'


OR ceilingcolor = 'GREEN'


OR wallcolor = 'YELLOW'





2)


SELECT z


FROM t


WHERE x = 6 AND y > 7 ;





3)


Find the average price of Bud.





Sells (bar, beer, price)





SELECT AVG (price)


FROM Sells


WHERE beer = 'Bud';



Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
  •  WhatsApp

SQL Introduction

 Sumit Kar     March 08, 2015     Programming, SQL     No comments   



SQL stands for Structured Query Language and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.





The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.




SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.


SQL Statements categories: 



DDL - Data Definition Language.



DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.


DDL Statements:



Create


It is used to create objects(tables, views) in the database.


Alter


It is used to alter the structure of the database objects.


Drop


delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).


Truncate


remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)


Grant


assigning privileges





DML - Data Manipulation Language.



DML is used to access, create, modify or delete data in the structures of the database.


DML Statements:



Select


Select data from the database


Insert


It is used to insert data into a table


Update


It is used to update existing data within a table


Delete


It removes rows from the table.





DCL - Data Control Language



Following are the examples of Data control Statements.


DCL Statements:



Commit


It will end the current transaction making the changes permanent and visible to all users..


Savepoint


It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back


Rollback


It will undo all the changes made by the current transaction.


Set- Transaction


It is used to define the properties of a transaction.


Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
  •  WhatsApp
Older Posts Home

Pages

  • Home

Trending Now

  • Write a Program to Add two 3x3 Matrix using C
    #include<stdio.h> void main () { int a [ 3 ][ 3 ], b [ 3 ][ 3 ], s [ 3 ][ 3 ], i , j ; printf ( "Enter the values of ...
  • C program for Unit Conversion
    /* Convert Celsius to Fahrenheit */ #include<stdio.h> void main() {     float c,f;     printf("Enter the temperature in Celcius: ...
  • Addition of two numbers on Server sent from Client [TCP] using C
    /* tcpClient.c */ #include <sys/types.h> #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #inc...
  • Write a Program to Print the Truth Table of Basic Gates using C
  • Write a Program to Add two 5x5 Matrix using C
    #include<stdio.h> void main() {   int a[5][5],b[5][5],c[5][5];   int i,j;   for(i=0;i<5;i++)   {     printf("\nEnter elements ...
  • Using the concept of Inheritance write a C++ Program to calculate the area and perimeter of rectangle
    /* C++ Program to calculate the area and perimeter of rectangles using concept of inheritance. */ #include using namespace std; class Re...
  • Concatenation of two strings sent from Client on the Server - [ TCP ] using C
    /* tcpClient.c */ #include <sys/types.h> #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #inc...
  • 8085 Programming: Exchange the contents of memory locations
    Exchange the contents of memory locations 2000H and 4000H. Program 1: LDA 2000H : Get the contents of memory location 2000H into accumulator...
  • Calculate Depreciation using C
    #include<conio.h> #include<stdio.h> void main () { float sv , pv , dep ; int yos ; clrscr (); printf ( "Enter the pu...
  • 8085 Programming: 1's COMPLEMENT OF A 16-BIT NUMBER
    The 16bit number is stored in C050,C051 The answer is stored in C052,C053   LXI H,C050   MOV A,M   CMA   STA C052   INX H   MOV ...

Blog Archive

  • ▼  2020 (1)
    • ▼  May (1)
      • Automating your deployments using Ansible
  • ►  2015 (92)
    • ►  October (4)
    • ►  September (3)
    • ►  August (3)
    • ►  July (9)
    • ►  June (9)
    • ►  May (20)
    • ►  April (7)
    • ►  March (22)
    • ►  February (7)
    • ►  January (8)
  • ►  2014 (158)
    • ►  November (70)
    • ►  October (6)
    • ►  September (82)
Powered by Blogger.

Categories

C - Programming Java Programming Basic Technology 8085 Assembly Programming For Loop Numerical Methods WhatsApp Algorithm Shell Programming Programming Networking Windows Android C++ Programming CPP If Else Tricky Internet Microsoft Pattern Photography Socket Program News While Loop Array DBMS DS Macro Recursion User Defined Function Conditional Operator Data Structure Durga Puja Earthquake Google Mela Nokia SQL Share Yahoo Airtel Bio Command Prompt Confused Facebook Finance Firefox Ganges Graph HokKolorob Input Kolkata MCQ Math Matrix NetNeutrality Oracle Religion Search Sumit Kar Survey Switch Case Viral Virus Visual Studio do-while featured featured_slider

Popular Programs

  • Write a Program to Add two 3x3 Matrix using C
    #include<stdio.h> void main () { int a [ 3 ][ 3 ], b [ 3 ][ 3 ], s [ 3 ][ 3 ], i , j ; printf ( "Enter the values of ...
  • C program for Unit Conversion
    /* Convert Celsius to Fahrenheit */ #include<stdio.h> void main() {     float c,f;     printf("Enter the temperature in Celcius: ...
  • Addition of two numbers on Server sent from Client [TCP] using C
    /* tcpClient.c */ #include <sys/types.h> #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #inc...
  • Write a Program to Print the Truth Table of Basic Gates using C
  • Write a Program to Add two 5x5 Matrix using C
    #include<stdio.h> void main() {   int a[5][5],b[5][5],c[5][5];   int i,j;   for(i=0;i<5;i++)   {     printf("\nEnter elements ...
  • Using the concept of Inheritance write a C++ Program to calculate the area and perimeter of rectangle
    /* C++ Program to calculate the area and perimeter of rectangles using concept of inheritance. */ #include using namespace std; class Re...
  • Concatenation of two strings sent from Client on the Server - [ TCP ] using C
    /* tcpClient.c */ #include <sys/types.h> #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #inc...
  • 8085 Programming: Exchange the contents of memory locations
    Exchange the contents of memory locations 2000H and 4000H. Program 1: LDA 2000H : Get the contents of memory location 2000H into accumulator...
  • Calculate Depreciation using C
    #include<conio.h> #include<stdio.h> void main () { float sv , pv , dep ; int yos ; clrscr (); printf ( "Enter the pu...
  • 8085 Programming: 1's COMPLEMENT OF A 16-BIT NUMBER
    The 16bit number is stored in C050,C051 The answer is stored in C052,C053   LXI H,C050   MOV A,M   CMA   STA C052   INX H   MOV ...

Daily Hits

Copyright © Sumit Kar