Virtual training week 4 structured query language (SQL)

1. Virtual training week 4structured query language (SQL)

19. Categories of SQL Statements • Data manipulation language (DML) statements begin with INSERT, UPDATE, DELETE, or MERGE and are used to modify the table by entering new rows, changing existing rows, or removing existing rows. • Data definition language (DDL) statements set up, change, and remove data structures from the database. The keywords CREATE, ALTER, DROP, RENAME, and TRUNCATE begin DDL statements. • Transaction control (TCL) statements are used to manage the changes made by DML statements. Changes to the data are executed using COMMIT, ROLLBACK, and SAVEPOINT. TCL changes can be grouped together into logical transactions. • Data control language (DCL) keywords GRANT and REVOKE are used to give or remove access rights to the database and the structures within it.

21. a SELECT statement must include the following: • A SELECT clause, which specifies the columns to be displayed • A FROM clause, which specifies the table containing the columns listed in the SELECT clause Select ………...[column name] From …….……..[table name]

22. A SELECT statement can do the following: • Projection: Used to choose columns in a table • Selection: Used to choose rows in a table • Join: Used to bring together data that is stored in different tables by creating a link between them (you will learn about this later!)

28. Aliases "friendly" name Column aliases let you rename columns in the output Why column Alias - Renames a column heading - Is useful with calculations - Immediately follows the column name - May have the optional AS keyword between the column name and alias - Requires double quotation marks if the alias contains spaces or special characters or is case-sensitive

30. Concatenation (||) Concatenation means to connect or link together in a series Concatenation means to connect or link together in a series Select columnname || columnname From tablename Select first_name || last_name From empolyees You cane use column alias to rename the connected columns.

32. DESCRIBE display the structure of a table. Why describe? DESCRIBE returns the table name, table schema, tablespace name, indexes, triggers, constraints, and comments as well as the data types, primary and foreign keys, and which columns can be nullable. DESC[RIBE] tablename; as DESCRIBE departments;

34. DISTINCT Used to eliminate duplicate rows. DISTINCT automatically displays the output in alphabetical order.

35. Limiting rows selected Where clause Used to limit the number of rows retrieved from the quarry note:  An alias cannot be used in the WHERE clause! A WHERE clause contains a condition that must be met, and it directly follows the FROM clause in a SQL statement.

37. SELECT*|{[DISTINCT] column|expression [alias],...}FROM table[WHEREcondition(s)]; Select * From employees Where salary = 1500 Note/ conditions in where clause is case sensitive

39. comparison operators used with where clause =   equal to >   greater than >= greater than or equal to <   less than <= less than or equal to <> not equal to can be (!= or ^ =(

44. The default sort order is ascending. - Numeric values are displayed lowest to highest. - Date values are displayed with the earliest value first. - Character values are displayed in alphabetical order. - Null values are displayed last in ascending order and first in descending order.

45. It is also possible to order data by using a column alias. The alias used in the SELECT statement is added to the ORDER BY clause. SELECT title, year AS "Recording Date" FROM d_cds ORDER BY "Recording Date"

46. What does this order by mean? Select first_name, last_name, salary, job_id From employees Where job_id = 10 Order by 3

47. GOOD LUCK SEE YOU NEXT MEETING Raafat Rashad raafat_rashad@yahoo.co.uk raafat_rashad@hotmail.com