Introduction to Structured Query Language(SQL )

1. Introduction to Structured Query Language(SQL) Prepared by Naveedullah Safi

2. What is SQL? • SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. • SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

3.  Four Main Categories Of SQL Statements

4. SQL Commands: • The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:

5. SQL Commands: DML- Data Manipulation Language: DDL - Data Definition Language:

6. SQL Syntax • SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax: • All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). • Important point to be noted is that SQL is not case sensitive, which means SELECT and select have same meaning in SQL statements.

7. Data manipulation language • DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database DML Examples • SELECT – retrieve data from the a database. • INSERT – insert data into a table. • UPDATE – updates existing data within a table. • DELETE – Delete all records from a database table.

8. SQL SELECT Statement: • The SELECT statement is used to select data from a database. • The result is stored in a result table, called the result-set. SELECT column1, column2....columnN FROM table_name;

9. SELECT Statement Example • The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table: • Example • SELECT CustomerName, City FROM Customers; SELECT * Example • The following SQL statement selects all the columns from the "Customers" table: Example • SELECT * FROM Customers;

10. INSERT INTO Statement • The INSERT INTO statement is used to insert new records in a table. • It is possible to write the INSERT INTO statement in two forms. • The first form does not specify the column names where the data will be inserted, only their values: • The second form specifies both the column names and the values to be inserted: INSERT INTO table_nameVALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...);

11. INSERT INTO Example • The following SQL statement inserts a new record in the "Customers" table: • Example • INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

12. UPDATE Statement • The UPDATE statement is used to update existing records in a table. • SQL UPDATE Syntax: • Notice the WHERE clause in the SQL UPDATE statement!The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value;

13. UPDATE Example • The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city. Example • UPDATE CustomersSET ContactName = 'Alfred Schmidt', City= 'Frankfurt'WHERE CustomerID = 1;

14. DELETE Statement • The DELETE statement is used to delete rows in a table. • SQL DELETE Syntax: • Notice the WHERE clause in the SQL DELETE statement!The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted! • It is possible to delete all rows in a table without deleting the table. DELETE FROM table_nameWHERE some_column=some_value; DELETE FROM table_name;orDELETE * FROM table_name;

15. DELETE Statement Example • The following SQL statement deletes the customer "AlfredsFutterkiste" from the "Customers" table: Example • DELETE FROM CustomersWHERE CustomerName='AlfredsFutterkiste';

16. The SQL WHERE Clause • The WHERE clause is used to filter records. • The WHERE clause is used to extract only those records that fulfill a specified condition. WHERE Syntax • SELECT column1, column2, ...FROM table_nameWHERE condition; • Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!

17. WHERE Clause Example • The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table: Example • SELECT * FROM CustomersWHERE Country='Mexico';

18. ORDER BY Keyword • The ORDER BY keyword is used to sort the result-set. • The ORDER BY keyword is used to sort the result-set by one or more columns. • The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. • SQL ORDER BY Syntax SELECT column_name,column_nameFROM table_nameORDER BY column_nameASC|DESC,column_name ASC|DESC;

19. ORDER BY Examples: The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column: SELECT * FROM CustomersORDER BY Country; The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column: SELECT * FROM CustomersORDER BY Country DESC; The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column: SELECT * FROM CustomersORDER BY Country, CustomerName; The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column: SELECT * FROM CustomersORDER BY Country ASC, CustomerName DESC;