Structured Query Language ( SQL)

1. Structured Query Language (SQL)

2. Objectives of SQL Ideally, a database language should allow a user to: • create the database and table structures; • perform basic data management tasks, such as the insertion, modification, and deletion of data from the tables; • perform both simple and complex queries. • perform these tasks with minimal user effort, and its command structure and syntax must be relatively easy to learn. • must be portable: that is, it must conform to some recognized standard  can use the same command structure and syntax when move from one DBMS to another

3. SQL is an example of a transform-oriented language, or a language designed to transform input tables into required output tables. • The ISO SQL standard has two major components: • Data Definition Language (DDL) for defining the database structure and controlling access to the data;  to create the database structure and the access mechanisms • Data Manipulation Language (DML) for retrieving and updating data.  to populate and query the tables

4. Writing SQL commands • An SQL statement consists of reserved words and user-defined words. • Reserved wordsare a fixed part of the SQL language and have a fixed meaning. • They must be spelled exactly as required and cannot be split across lines. • User-defined wordsare made up by the user (according to certain syntax rules), and represent the names of various database objects such as tables, columns, views, indexes, and so on. • Most components of an SQL statement are case insensitive

5. The SQL language • The Data Definition Language (DDL): This subset of SQL supports the creation, deletion, and modification of definitions for tables and views. • The Data Manipulation Language (DML): This subset of SQL allows users to pose queries and to insert, delete, and modify rows. • Triggers: actions executed by the DBMS whenever changes to the database meet conditions specified in the trigger.

6. Data manipulation • SQL DML statements: • SELECT to query data in the database; • INSERT to insert data into a table; • UPDATE to update data in a table; • DELETE to delete data from a table

7. THE FORM OF A BASIC SQL QUERY SELECT [DISTINCT] select-list FROM from-list WHERE qualification

8. a SELECT clause : species columns to be retained in the result, • a FROM clause : species a cross-product of tables. • The optional WHERE clause : selection conditions on the tables mentioned in the FROM clause.

9. Queries: SELECT • The purpose : is to retrieve and display data from one or more database tables. • The form: • columnExpression represents a column name or an expression; • newName is the column name as a display heading; • TableName is the name of an existing database table or view • alias is an optional abbreviation for TableName

10. The sequence of processing in a SELECT statement is: • SELECT specifies which columns are to appear in the output; • FROM specifies the table or tables to be used; • WHERE filters the rows subject to some condition; • GROUP BY forms groups of rows with the same column value; • HAVING filters the groups subject to some condition; • ORDER BY specifies the order of the output.

11. Example:

12. Sailors Reserves Boats

13. Find the names and ages of all sailors.

14. Find the names and ages of all sailors.

15. Example : Find all sailors with a rating above 7

16. Find all sailors with a rating above 7

17. The syntax of a basic SQL query • The from-list in the FROM clause is a list of table names. • A table name can be followed by a range variable • The select-list is a list of column names of tables named in the from list. • Column names can be prefixed by a range variable. • The qualification in the in the WHERE clause is a boolean combination of conditions of the form expression op expression • Op : comparison expression • An expression is a column name, a constant or an (arithmetic or string) expression • The DISTINCT keyword is optional. • Indicate that the table computed as an answer to this query should not contain duplicate.

18. The Conceptual evaluation strategy: • Compute the cross-product of the tables in the from-list. • Delete those rows in the cross-product that fail the qualification conditions. • Delete all columns that do not appear in the select-list. • If DISTINCT is specified, eliminate duplicate rows.

19. Find the names of sailors who have reserved boat number 103

20. Find the names of sailors who have reserved boat number 103

21. Explanation: Reserves Sailors Reserves X Sailors

22. The answer :

23. Find the namesof sailors who have reserved a red boat

24. Find the Sids of sailors who have reserved a red boat

25. Find the colors of boats reserved by Lubber

26. Find the colors of boats reserved by Lubber

27. Find the names of sailors who have reserved at least one boat

28. Find the names of sailors who have reserved at least one boat

29. UNION, INTERSECT, AND EXCEPT Find the names of sailors who have reserved a red or a green boat.

30. Find the names of sailors who have reserved a red or a green boat.

31. Find the names of sailors who have reserved both a red and a green boat.

32. Find the names of sailors who have reserved both a red and a green boat.

33. Find the sids of all sailors who have reserved red boats but not green boats

34. Find the sids of all sailors who have reserved red boats but not green boats