1 / 19

Coding:

Coding:. SQL S tructured Q uery L anguage. SQL. Select command. Employee:. SELECT ID,ENAME, BDATE,SALARY,DNO FROM Employee;. Query result. The Where condition. Employee:. SELECT ID,ENAME, BDATE,SALARY,DNO FROM Employee WHERE Salary >=1000;. Query result. SELECT Statement.

winola
Download Presentation

Coding:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related