[PDF] how to insert data into table
[PDF] how to know the size of array java
[PDF] how to learn formal languages and automata theory
[PDF] how to make 2 formalin
[PDF] how to make a map in google earth pro
[PDF] how to make a triangle symbol on mac
[PDF] how to make an element constructor in minecraft
[PDF] how to make angle symbol on mac
[PDF] how to make antidote in minecraft
[PDF] how to make chlorine in minecraft
[PDF] how to make foreign letters on keyboard mac
[PDF] how to make glow sticks glow brighter
[PDF] how to make phosphorus in minecraft
[PDF] how to make scientific figures for publication
[PDF] how to make scientific figures in powerpoint
Lab 2 SQL
Data Manipulation Language
(DML)
Eng. Ibraheem Lubbad
The Islamic University of Gaza
Faculty of Engineering
Dept. of Computer Engineering
Database Lab (ECOM 4113)
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)
SELECT Statement:
The SELECT statement retrieves data from a database. The data is returned in a table-like structure called a result-set. SELECT is the most frequently used action on a database. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name.
The whole query is called SQL SELECT Statement.
-table-name : is the name of the table from which the information is retrieved. -column_list : includes one or more columns from which data is retrieved. -The code within the brackets is optional.
¾ Retrieve data by specific column:
Syntax of SQL Select Statement
SELECT * | {[DISTINCT] column_list | expression [alias],...}
FROM table-name
[WHERE condition] [GROUP BY columns] [HAVING group-selection-condition] [ORDER BY column-names || aliases || column-numbers];
Select Specific Column
SELECT NAME, DEPT_NAME FROM INSTRUCTOR;
¾ 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;
SELECT NAME, DEPT_NAME, SALARY, SALARY/100
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 edžpression (inǀolǀing, for edžample н, о, כ any of the input values is null
SELECT ID, COURSE_ID,GRADE
FROM TAKES
WHERE GRADE IS NULL;
SELECT NULL*20
FROM DUAL
¾ A Column Alias:
Renames a column heading
Is useful with calculations
Immediately follows the column name (There can also bethe optional AS keyword between the column name andalias.) Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive
¾ A Concatenation Operator
Links columns or character strings to other columns
Is represented by two vertical bars (|| )
Creates a resultant column that is a character expression SELECT TIME_SLOT_ID ,Start_HR || ':'||Start_Min as STartTime ,ENd_hr || ':' || End_min as EndTime from Time_slot;
¾ 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
SELECT NAME
FROM INSTRUCTOR
WHERE DEPT_NAME = 'COMP. SCI.';
Notes:
When you deal with character strings or date values, you must enclosed 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
9 Percent (%): The % character matches any substring
9 Underscore (_ ): The character matches any character.
Example: Find the names of all student whose name with second and third character ͞an"
Comparison operators:
Operator Operator Description
= Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to
BETWEEN ... AND... Between two values (inclusive)
IN(set) Match any of a list of values
ANY(set) Compare to any value in a set
ALL(set) Compare to all values in a set
[NOT] LIKE Match a character pattern
IS[NOT] NULL Is a null value
Rules of Precedence:
Operator Priority
Parentheses () 1
Arithmetic operators / , * 2
+ , - 3
Concatenation operator || 4
Comparison conditions <,> ,>=, <=,<> 5
IS [NOT] NULL, LIKE, [NOT] IN 6
[NOT] BETWEEN 7
Not equal to 8
NOT logical condition 9
AND logical condition 10
OR logical condition 11
Note: <> all is identical to not in, whereas = all is not the same as in Example: find the names of instructors with salary amounts between $90,000 and $100,000.
Instead of:
Example: Find all the names of instructors whose names are neither ͞Mozart" nor
͞Einstein".
SELECT NAME
FROM INSTRUCTOR
WHERE SALARY BETWEEN 90000 AND 100000;
SELECT NAME
FROM INSTRUCTOR
WHERE SALARY <= 100000 AND SALARY >= 90000;
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: retrieǀing all departments' records sorted by budget. By default, ͞ORDER BY" Clause sorts the retrieǀed rows in ascending order. To reǀerse the ordering, use ͞DESC" keyword after column-name.
SELECT DEPT_NAME,BUILDING,BUDGET
FROM DEPARTMENT
ORDER BY BUDGET DESC;
SELECT DEPT_NAME,BUILDING,BUDGET
FROM DEPARTMENT
ORDER BY BUDGET ;
You can also sort rows according to expression result; in this case, you have to use an alias instead of column name also, sort rows according to more than one column. Example: Find all instructor sorted by their monthly salary. Example: Find all student sorted by their department name , if there are two student have the same department name , then sort them by total credit in ascending order, then by their ͞student name" alias.
Sorting According to More Than One Column
SELECT NAME "STUDNET NAME ´DEPT_NAME,TOT_CRED
FROM STUDENT
ORDER BY DEPT_NAME, TOT_CRED DESC, "STUDNET NAME" ;
SELECT NAME , DEPT_NAME ,SALARY/12 MONTHLY
FROM INSTRUCTOR
ORDER BY MONTHLY
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:
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. 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 (
Example SQL INSERT Statement
INSERT INTO CLASSROOM VALUES (
INSERT INTO SECTION
VALUES ('CS-348', 1, 'FALL', 2016, 'AL QUDS', '218', 'A');
¾ Use select with insert statement
Q) Enroll every student in ' Database CS-347' course taken in 2009, in the above section.
DELETE Statement
DELETE statement is used to delete rows from a table according to a specified condition.
Example:
Note: It also can be used to delete all rows from a table by not specifying any conditions. If you want to empty a table, you just have to issue this command: ͞DELETE FROM table-name". You should be aware when using this form. Q) Delete enrollments in the above section where the student's name is contain
Syntax of SQL DELETE Statement
DELETE FROM table-name
WHERE condition;
Using DELETE Statement
DELETE FROM CLASSROOM
WHERE BUILDING = 'AL QUDS'
INSERT INTO TAKES (ID, COURSE_ID,SEC_ID,SEMESTER,YEAR)
SELECT ID, 'CS-348','1', 'FALL',2016
FROM TAKES
WHERE COURSE_ID='CS-347';
Constant columns
Insert constant value
DELETE FROM TAKES WHERE (ID ,COURSE_ID) IN (
SELECT ID,'CS-348'
FROM STUDENT
WHERE NAME LIKE '%HAN%');
Constant columns
Insert constant value
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;
Using UPDATE Statement
UPDATE COURSE
SET CREDITS=3
WHERE COURSE_ID='CS-190'
quotesdbs_dbs6.pdfusesText_12
[PDF] SQL INSERT INTO