Database Fundamentals

College Requirements -Compulsive Courses

CSIT2204

Iman siddig Adam

imanuofb@[Link]
Insert, update and delete data
 DESC DEPARTMENT;
 INSERT INTO DEPARTMENT
(DNAME,DNUMBER,Mgr_start_date) VALUES
('Research',5, '22-MAY-88');
 INSERT INTO DEPARTMENT
(DNAME,DNUMBER,Mgr_start_date)VALUES
('Administration',4, '01-JAN-95');
 INSERT INTO DEPARTMENT
(DNAME,DNUMBER,Mgr_start_date)VALUES
('Headquarters', 1, '19-JUN-81');
 commit;

Database Lab (1) 5

 INSERT INTO EMPLOYEE VALUES ( 'James', 'E', 'Borg', 888665555,
'10-NOV-37', '450 Stone, Houston, TX', 'M', 55000, NULL,1 );
 INSERT INTO EMPLOYEE VALUES ( 'Franklin', 'T', 'Wong', 333445555,
'12-AUG-55', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);
 INSERT INTO EMPLOYEE VALUES ( 'Jennifer', 'S', 'Wallace', 987654321,
'20-JUN-41', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4);
 INSERT INTO EMPLOYEE VALUES ( 'John', 'B', 'Smith', 123456789, '01-
SEP-65', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5);
 INSERT INTO EMPLOYEE VALUES ( 'Alicia', 'J', 'Zelaya', 999887777, '19-
JAN-68', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4);
 INSERT INTO EMPLOYEE VALUES ( 'Ramesh', 'K', 'Narayan', 666884444,
'15-SEP-62', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5);
 INSERT INTO EMPLOYEE VALUES ( 'Joyce', 'A', 'English', 453453453, '31-
JUL-72', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5);
 INSERT INTO EMPLOYEE VALUES ( 'Ahmad', 'V', 'Jabbar', 987987987,
'29-MAR-69', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4);
 commit;

Database Lab (1) 6

 UPDATE DEPARTMENT SET
Mgr_ssn=333445555 WHERE Dnumber = 5;
 UPDATE DEPARTMENT SET
Mgr_ssn=987654321 WHERE Dnumber = 4;
 UPDATE DEPARTMENT SET
Mgr_ssn=888665555 WHERE Dnumber = 1;
 commit;

Database Lab (1) 7

 INSERT INTO DEPT_LOCATIONS VALUES
(1,'Houston');
 INSERT INTO DEPT_LOCATIONS VALUES
(4,'Stafford');
 INSERT INTO DEPT_LOCATIONS VALUES
(5,'Bellaire');
 INSERT INTO DEPT_LOCATIONS VALUES
(5,'Sugarland');
 INSERT INTO DEPT_LOCATIONS VALUES
(5,'Houston');
 commit;

Database Lab (1) 8

 INSERT INTO PROJECT VALUES ('ProductX', 1,
'Bellaire', 5);
 INSERT INTO PROJECT VALUES ('ProductY', 2,
'Sugarland', 5);
 INSERT INTO PROJECT VALUES ('ProductZ', 3,
'Houston', 5 );
 INSERT INTO PROJECT VALUES ('Computerization',
10 ,'Stafford',4 );
 INSERT INTO PROJECT VALUES ('Reorganization ', 20,
'Houston',1);
 INSERT INTO PROJECT VALUES ('Newbenefits', 30,
'Stafford' ,4);
 commit;

Database Lab (1) 9

 INSERT INTO WORKS_ON VALUES (123456789, 1 ,32.5 );
 INSERT INTO WORKS_ON VALUES (123456789, 2 ,7.5 );
 INSERT INTO WORKS_ON VALUES (666884444, 3 ,40.0 );
 INSERT INTO WORKS_ON VALUES (453453453, 1 ,20.0 );
 INSERT INTO WORKS_ON VALUES (453453453, 2 ,20.0 );
 INSERT INTO WORKS_ON VALUES (333445555, 2 ,10.0 );
 INSERT INTO WORKS_ON VALUES (333445555, 3 ,10.0 );
 INSERT INTO WORKS_ON VALUES (333445555, 10, 10.0 );
 INSERT INTO WORKS_ON VALUES (333445555, 20, 10.0 );
 INSERT INTO WORKS_ON VALUES (999887777, 30, 30.0 );
 INSERT INTO WORKS_ON VALUES (999887777, 10, 10.0 );
 INSERT INTO WORKS_ON VALUES (987987987, 10, 35.0 );
 INSERT INTO WORKS_ON VALUES (987987987, 30, 5.0 );
 INSERT INTO WORKS_ON VALUES (987654321, 30, 20.0 );
 INSERT INTO WORKS_ON VALUES (987654321, 20, 15.0 );
 INSERT INTO WORKS_ON VALUES (888665555, 20, NULL );

Database Lab (1) 10

 INSERT INTO DEPENDENT VALUES (333445555,
'Alice', 'F', '05-APR-86', 'Daughter');
 INSERT INTO DEPENDENT VALUES (333445555, 'Joy',
'F', '03-MAY-58', 'Spouse');
 INSERT INTO DEPENDENT VALUES (987654321,
'Abner', 'M', '28-FEB-42', 'Spouse');
 INSERT INTO DEPENDENT VALUES (123456789,
'Michael', 'M', '04-JAN-88', 'Son');
 INSERT INTO DEPENDENT VALUES (123456789,
'Alice', 'F', '30-DEC-88', 'Daughter');
 INSERT INTO DEPENDENT VALUES (123456789,
'Elizabeth','F', '05-MAY-67', 'Spouse');
 commit;

Database Lab (1) 11

Writing Basic SQL SELECT Statements
Projection Selection

Table 1 Table 1

Join

Table 1 Table 2
 Syntax

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

 SELECT identifies what columns

 FROM identifies which table
SELECT *
FROM employee;
SELECT Fname, Ssn, Salary
FROM employee;
 SQL statements are not case sensitive.
 SQL statements can be on one or more lines.
 Keywords cannot be abbreviated or split
across lines.
 Clauses are usually placed on separate lines.
 Indents are used to enhance readability.
Create expressions with number and date data by using
arithmetic operators.

Operator Description

+ Add

- Subtract

* Multiply

/ Divide
SELECT Fname, Ssn, Salary, Salary +1000
FROM employee;
SELECT Fname, Ssn, Salary, 12*(Salary+100)
FROM employee
 A null is a value that is unavailable, unassigned,
unknown, or inapplicable.
 A null is not the same as zero or a blank space.
SELECT Fname, Super_ssn
FROM employee
A column alias:
 Renames a column heading
 Is useful with calculations
 Immediately follows the column name - there
can also be the optional AS keyword between
the column name and alias
 Requires double quotation marks if it contains
spaces or special characters or is case sensitive
SELECT Fname As Name, Ssn As Employee_Number, Salary
FROM employee;
A concatenation operator:
 Concatenates columns or character strings
to other columns
 Is represented by two vertical bars (||)
 Creates a resultant column that is a
character expression
SELECT last_name||job_id AS "Employees"
FROM employees;

…
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;

…
The default display of queries is all rows, including
duplicate rows.
SELECT Dno
FROM employee
Eliminate duplicate rows by using the DISTINCT
keyword in the SELECT clause.
SELECT DISTINCT Dno
FROM employee
Syntax
SELECT *
FROM table;

SELECT column1, column2, column3

FROM table;

SELECT column1, column2*100 AS new_Name

FROM table;

SELECT DISTINCT column

FROM table;
EMPLOYEES

“retrieve all
employees
in department 5”
• Restrict the rows returned by using the
WHERE clause.
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

• The WHERE clause follows the FROM clause.

SELECT Fname, Bdate,Salary, Dno
FROM employee
WHERE Dno=5
• Character strings and date values are enclosed in
single quotation marks.
• Character values are case sensitive, and date
values are format sensitive.
• The default date format is DD-MON-RR.

SELECT Fname, Bdate,Salary, Dno

FROM employee
WHERE Fname=‘Mohammed’
Operator Meaning

= Equal to

> Greater than

>= Greater than or equal to

< Less than

<= Less than or equal to

<> Not equal to

SELECT lname, Bdate,Salary, Dno
FROM employee
WHERE Salary>35000
Operator Meaning

BETWEEN Between two values (inclusive),

...AND...

IN(set) Match any of a list of values

LIKE Match a character pattern

IS NULL Is a null value

Use the BETWEEN condition to display rows based on a range
of values.
SELECT lname, Bdate,Salary, Dno
FROM employee
WHERE salary BETWEEN 20000 AND 35000

Lower limit Upper limit

Use the IN membership condition to test for values in
a list.
SELECT lname, Bdate,Salary, Dno
FROM employee
WHERE Dno IN (1,5)
• Use the LIKE condition to perform wildcard
searches of valid search string values.
• Search conditions can contain either literal
characters or numbers:
– % denotes zero or many characters.
– _ denotes one character.

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
• You can combine pattern-matching
characters.
SELECT lname, Bdate,Salary, Dno
FROM employee
WHERE lname LIKE 'W_%'

• You can use the ESCAPE identifier to search

for the actual % and _ symbols.
Test for nulls with the IS NULL operator.

SELECT *
FROM employee
WHERE Super_ssn IS NULL
Operator Meaning

AND Returns TRUE if both component

conditions are true
OR Returns TRUE if either component
condition is true

NOT Returns TRUE if the following

condition is false
AND requires both conditions to be true.
SELECT lname,dno, Salary
FROM employee
WHERE salary >=25000
AND lname LIKE '%a%'
OR requires either condition to be true.
SELECT lname,dno, Salary
FROM employee
WHERE salary >=25000
OR lname LIKE '%a%'
SELECT fname, salary, dno
FROM employee
WHERE Salary
NOT IN (25000, 35000, 45000,55000)
Order Evaluated Operator
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT logical condition
7 AND logical condition
8 OR logical condition

Override rules of precedence by using parentheses.

SELECT fname, dno, salary
FROM employee
WHERE dno = 4
OR dno = 5
AND salary<=35000
Database Lab (1) 49
• Sort rows with the ORDER BY clause
– ASC: ascending order, default
– DESC: descending order
• The ORDER BY clause comes last in the SELECT
statement.
SELECT lname,dno, Salary
FROM employee
ORDER BY salary;
SELECT lname,dno, Salary,Bdate
FROM employee
ORDER BY Bdate DESC;

…
SELECT ssn, lname, salary*12 annsal
FROM employee
ORDER BY annsal
• The order of ORDER BY list is the order of sort.
SELECT lname,dno, Salary
FROM employee
ORDER BY dno, salary DESC

• You can sort by a column that is not in the

SELECT list.
Data Query Language (select)

Database Lab (1) 54