Coding:

1. Coding: SQL Structured Query Language Dr. Mohamed Hegazi

2. SQL Dr. Mohamed Hegazi

3. Select command Employee: SELECT ID,ENAME, BDATE,SALARY,DNO FROM Employee; Query result Dr. Mohamed Hegazi

4. The Where condition Employee: SELECT ID,ENAME, BDATE,SALARY,DNO FROM Employee WHERE Salary >=1000; Query result Dr. Mohamed Hegazi

5. SELECT Statement • SQL has one basic statement for retrieving information from the database: SELECT statement • In the SELECT statement, users specify what the result of the query should be SELECT Basic Syntax SELECT <attribute list> FROM <table list> [WHERE <condition>] ; <attribute list> is a list of attribute names whose values are to be retrieved by the query (or * for all attribute) <table list> is a list of relation names required to process the query <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query Dr. Mohamed Hegazi

6. Simple SELECT Example SELECT EMP_ ADDRESS FROM EMPLOYEE WHERE EMP_ NAME = “Joe Bates” Dr. Mohamed Hegazi

7. Selection Example List the last names of employees working in department number 3. SELECT ENAME FROM EMPLOYEE WHERE Dno = 3 Query result Dr. Mohamed Hegazi

8. Selection Example 1. List the all data of employees who work in department 3 and earn over 1000, or work in department 1 and earn over 1100. SELECT * FROM EMPLOYEE WHERE (Dno =3 and Salary > 1000) or (Dno = 1 and Salary > 1100); 2. List the Enames of employees who work in department 3 and earn over 1000, or work in department 1 and earn over 1000. SELECT EName FROM EMPLOYEE WHERE (Dno =3 and Salary > 1000) or (Dno = 1 and Salary > 1100); Query result Dr. Mohamed Hegazi

9. Selection Example - Multiple Tables • List the Enames of employees working in the “Research” department. Department: Employee: Query result SELECT ENAME FROM EMPLOYEE, DEPARTMENT WHERE Dno= Dnumber and Dname = “Research”; Dr. Mohamed Hegazi

10. Distinct • List the DNO from employees Query result Employee: SELECT DNO FROM EMPLOYEE; SELECT DISTICT DNO FROM EMPLOYEE; Query result Dr. Mohamed Hegazi

11. LIKE {%,-} List the Enames and the salary of employees in whish the first letter of his name is A Employee: SELECT ename, salary FOM EMPLOYEE WHERE ename LIKE ‘A%’; Query result SELECT ename, salary FOM EMPLOYEE WHERE ename LIKE ‘%a’; Query result Dr. Mohamed Hegazi

12. Data Manipulation Language(DML) UPDATE INSERT UPDATE tatement is used to modify attribute values of one or more selected tuples in a relation INSERT statement is used to add tuples to an existing relation DELETE DELETE statement is used to remove existing tuples from a relation Dr. Mohamed Hegazi

13. INSERT Example(1):(without attribute name); INSERT INTO EMP VALUES(108,’MOHAMED’, MUSCAT,1000,2); Example(2):(with attribute name); INSERT INTO EMP (ID,ENAME,ADDRESS,SALARY,DPNO) VALUES(108,’MOHAMED’, ‘MUSCAT’,1000,2); Example(3):(some of attribute ); INSERT INTO EMP (ENAME,ID) VALUES(’MOHAMED’, 108); Example(4):(NULL value); INSERT INTO EMP VALUES(108,’MOHAMED’, NULL,1000,2); INSERT Syntax INSERT INTO <table name> [(< column name> {, <column name> })] (VALUES (< constant value>, {,< constant value> })| <select statement>); Dr. Mohamed Hegazi

14. DELETE Example(1): DELETE FROM EMPLOYEE WHERE DNO = 5; Example(2): DELETE FROM EMPLOYEE; OR: DELETE * FROM EMPLOYEE DELETE Syntax: DELETE FROM <table name> [WHERE <select condition>]; NOTE: A single DELETE statement may delete zero, one, several or all tuples from a table Dr. Mohamed Hegazi

15. UPDATE Example (1): UPDATE EMP SET SALARY = SALARY * 1. 1 WHERE ENAME = ‘AHMED’; Example (2): UPDATE EMP SET SALARY = SALARY * 12 ; UPDATE Syntax: UPDATE <table name> SET <column name> = <value expression> {, <column name> = <value expression>} [WHERE <select condition>]; Dr. Mohamed Hegazi

16. Data Definition Language (DDL) DROP CREATE ALTER DROP TABLEDelete table: – Drops all constraints defined on the table – Deletes all tuples within the table – Removes the table definition from the system catalog CREATE TABLE statement creates a new relation, by specifying its name, attributes and constraints, and records the table definition in the system catalog ALTER TABLE is used for schema evolution. - a table created using the CREATE TABLE command, can be changed using the ALTER TABLE command Dr. Mohamed Hegazi

17. CREATE Example: CREATE TABLE EMPLOYEE (ID CHAR (9) PRIMARY KEY, ENAME VARCHAR (15) NOT NULL, BDATE DATE, ADDRESS VARCHAR (30), SALARY DECIMAL (10, 2), DNO INT NOT NULL ); CREATE TABLE Syntax CREATE TABLE <table name> (< column name> <column type>[< attribute constraint>] [< table constraint> {, <table constraint>} ] ); Dr. Mohamed Hegazi

18. ALTER Adding or dropping constraints Adding or dropping a column: Changing a column definition Example (1) (add column) ALTER TABLE EMP ADD JOB VACHAR2(12); NOTE:To add an attribute (Value in all tuples will be initially NULL, so NOT NULL cannot be specified) Example (2) (delete column) ALTER TABLE EMP DROP JOB; Example ALTER TABLE EMP MODIFY ID CHAR(10); Dr. Mohamed Hegazi

19. DROP TABLE Drop table syntax: DROP TABLE <table name> Example: DROP TABLE EMPLOYEE; Dr. Mohamed Hegazi