SQL Data Manipulation Language (DML), Study notes of Engineering

Download SQL Data Manipulation Language (DML) and more Study notes Engineering in PDF only on Docsity!

Islamic University Of Gaza Faculty of Engineering Computer Engineering Department Database Lab (ECOM 4113)

Lab 2

SQL

Data Manipulation Language

(DML)

Eng. Ibraheem Lubbad

SQL stands for Structured Query Language, it’s a standard language for accessing

and manipulating databases.

SQL commands are case insensitive instructions used to communicate with the

database to perform specific tasks, work, functions and queries with data. All 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 (;)

SQL commands are grouped into major categories depending on their

functionality:

 Data Manipulation Language (DML) - These SQL commands are used

for storing, retrieving, modifying, and deleting data. These Data

Manipulation Language commands are CALL, DELETE, EXPLAIN,

INSERT, LOCK TABLE, MERGE, SELECT and UPDATE.

 Data Definition Language (DDL) - These SQL commands are used for

creating, modifying, and dropping the structure of database objects.

The commands are ALTER, ANALYZE, AUDIT, COMMENT, CREATE, DROP,

FLASHBACK, GRANT, PURGE, RENAME, REVOKE and TRUNCATE.

 Transaction Control Language (TCL) - These SQL commands are used

for managing changes affecting the data. These commands are

COMMIT, ROLLBACK, and SAVEPOINT.

 Data Control Language (DCL) - These SQL commands are used for

providing security to database objects. These commands are GRANT and

REVOKE.

In our lab we will use university schema (you can open it by click on file)

 Retrieve data For all column using (*):

 Arithmetic Expressions:

Arithmetic operators can perform arithmetical operations on numeric

operands involved. Arithmetic operators are addition (+), subtraction (-),

multiplication (*) and division (/). The + and - operators can also be used in

date arithmetic

Use * To Select All Column SELECT * FROM INSTRUCTOR; Use * To Select All Column SELECT NAME, DEPT_NAME, SALARY, SALARY/ FROM INSTRUCTOR;

 Null Values:

 A null is value is unknown or does not exist

 It is NOT the same as a zero or a blank space.

 The result of any arithmetic expressions containing a null value is a

Null value.

You cannot use equal operator (=) to compare two null values! Instead, use (IS)

with special keyword null to check if a value is null or not.

Example: retrieve all student who his grade has not been awarded

The result of an arithmetic expression (involving, for example +, −, ∗, or /) is null if

any of the input values is null

Use * To Select All Column SELECT ID, COURSE_ID,GRADE FROM TAKES WHERE GRADE IS NULL; Use * To Select All Column SELECT NULL* FROM DUAL

 SELECT DISTINCT Statement

 SELECT DISTINCT returns only distinct (different) values.

 SELECT DISTINCT eliminates duplicate records from the results.

 DISTINCT operates on a single column. DISTINCT for multiple columns is not

supported

 SQL WHERE Clause:

 To limit the number of rows use the WHERE clause.

 The WHERE clause filters for rows that meet certain criteria.

 WHERE is followed by a condition that returns either true or false.

 WHERE is used with SELECT, UPDATE, and DELETE.

Example: Find the names of all instructors in the Computer Science department Syntax of SQL INSERT Statement SELECT NAME FROM INSTRUCTOR WHERE DEPT_NAME = 'COMP. SCI.';

Notes:

 When you deal with character strings or date values, you must enclosed

them by single quotation marks ( ‘ ’ )

 Character values are case-sensitive, and date values are format-sensitive.

SQL WHERE LIKE Statement:

 Pattern matching can be performed on strings, use the like statement  Patterns are case sensitive  describe patterns by using two special characters  Percent (%): The % character matches any substring  Underscore (_ ): The character matches any character. Example: Find the names of all student whose name starts with ‘S’.

Example: Find the names of all student whose name with second and third

character “an”

Example: find the names of instructors with salary amounts between $90,000 and

Instead of:

Example: Find all the names of instructors whose names are neither “Mozart” nor

“Einstein”.

Syntax of SQL INSERT Statement SELECT NAME FROM INSTRUCTOR WHERE SALARY BETWEEN 90000 AND 100000; Syntax of SQL INSERT Statement SELECT NAME FROM INSTRUCTOR WHERE SALARY <= 100000 AND SALARY >= 90000; Syntax of SQL INSERT Statement SELECT DISTINCT NAME FROM INSTRUCTOR WHERE NAME NOT IN ('MOZART', 'EINSTEIN');

 Sort:

You can sort rows retrieved by a query using the optional [ORDER BY clause].

Example: retrieving all departments’ records sorted by budget.

By default, “ORDER BY” Clause sorts the retrieved rows in ascending order. To

reverse the ordering, use “DESC” keyword after column-name.

Syntax of SQL INSERT Statement SELECT DEPT_NAME,BUILDING,BUDGET FROM DEPARTMENT ORDER BY BUDGET DESC; Syntax of SQL INSERT Statement SELECT DEPT_NAME,BUILDING,BUDGET FROM DEPARTMENT ORDER BY BUDGET ;

Note: in “ORDER BY” clause, you can type (column | alias) numeric position

instead of name.

For example in the previous query, department name column comes first in the

query, so its number is 1. total credit comes second, so its number is 2. Finally,

“student name” alias comes third, so its number is 3. Therefore, the

previous query can be wrote in another way:

Sorting According to More Than One Column SELECT NAME "STUDNET NAME" ,DEPT_NAME,TOT_CRED FROM STUDENT ORDER BY 2,3 DESC, 1 ;

INSERT Statement:

INSERT statement is used to add new rows into a table in the database.

You can also insert new rows without specifying column names, by typing:

“ INSERT INTO ” table-name VALUES (value-list)”. In this case, you MUST order

values in the same order of its corresponding columns.

Q) Create a section of ‘Database lab CS-348’ course in fall 2016, with sec id of 1

and class room al Quds 218.

Syntax of SQL INSERT Statement INSERT INTO Table-Name (column_list) VALUES (values-list); Example SQL INSERT Statement INSERT INTO CLASSROOM (BUILDING, ROOM_NUMBER, CAPACITY) VALUES ( 'AL QUDS’, 218, 25) Example SQL INSERT Statement INSERT INTO CLASSROOM VALUES ( 'AL QUDS’, 218, 25) Syntax of SQL DELETE Statement INSERT INTO SECTION VALUES ('CS-348', 1 , 'FALL', 2016 , 'AL QUDS', '218', 'A');

UPDATE Statement

UPDATE statement is used to modify existing rows values in a table.

Example: END Syntax of SQL UPDATE Statement UPDATE TABLE-NAME SET COLUMN_NAME_1 = NEW_VALUE1 , COLUMN_NAME_2 = NEW_VALUE2 -- WHERE CONDITION; Use UPDATE Statement UPDATE COURSE SET CREDITS= WHERE COURSE_ID='CS-190'