Basic commands 1

SQL SELECT Statement

• SELECT * FROM table_name; --(selects all columns in the table)
• SELECT column_name(s) FROM table_name;
• SELECT LastName,FirstName FROM Persons;

The SELECT DISTINCT Statement


To SELECT unique values from a table eliminating the duplicates.

• SELECT DISTINCT column_name(s) FROM table_name; 
• SELECT DISTINCT Company FROM Orders;

SQL WHERE Clause

 The where clause is used for conditional selection of statements.

 

•  SELECT column FROM table WHERE column operator value;
•  SELECT * FROM Persons WHERE City='Sandnes';

 You have give the selection Keyword inside the quotes.

 you can use different types of condition in where clause

Operator Description

= Equal
<> Not equal

 > Greater than

< Less than

 >= Greater than or equal

 <= Less than or equal

 BETWEEN Between an inclusive range

 LIKE Search for a pattern

 IN If you know the exact value you want to return for at least one of the columns

 The LIKE Condition

• SELECT column FROM table WHERE column LIKE pattern;

Can use percentage symbol to initate the query to retrive sort based on first letter or last letter;

• SELECT * FROM Persons WHERE FirstName LIKE 'O%';

• SELECT * FROM Persons WHERE FirstName LIKE '%a';

• SELECT * FROM Persons WHERE FirstName LIKE '%la%'

Can add and condition in where clause to add more then one condition

  

SELECT CompanyName, ContactName FROM customers WHERE CompanyName > 'g' AND ContactName > 'g'

 SQL INSERT INTO Statement

Inserts values into the table

• INSERT INTO table_name VALUES (value1, value2,....);

• INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....);

 SQL UPDATE Statement

 Update the existing column value in a table

• UPDATE table_name SET column_name = new_value WHERE column_name = some_value;

• UPDATE Person SET FirstName = 'abc' WHERE LastName = 'efg'

• UPDATE Person SET Address = 'Dubai', City = 'Karama' WHERE LastName = 'EFG'

SQL DELETE Statement

• DELETE FROM table_name WHERE column_name = some_value;

• DELETE FROM Person WHERE LastName = 'EFG';

• DELETE FROM table_name

or

DELETE * FROM table_name

 Sorting in sql

SQL ORDER BY

• SELECT Company, OrderNumber FROM Orders ORDER BY Company;

• SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;

• SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC;

• SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC;

SQL AND & OR
AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.

• SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson';

• SELECT * FROM Persons WHERE firstname='Tove' OR lastname='Svendson';

• SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson';

SQL IN

 The IN operator may be used if you know the exact value you want to return for at least one of the columns.

 • SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

• SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')

SQL BETWEEN

• The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

• SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;

• SELECT * FROM Persons WHERE Salary BETWEEN '10000' AND '150000';

• SELECT * FROM Persons WHERE Salary NOT BETWEEN '10000' AND '150000';

SQL Alias

can assign different names to the columns and tables

• SELECT column AS column_alias FROM table;

• SELECT last_name as NAME from emp;

• SELECT LastName, FirstName FROM Persons AS Employees;