Database Systems Lab
Mid Summary
ّ
هذا الملف تم حله من قبل الطالب :ليث عمرو
هذه الحلول ه جهد ر
بشي قابل للخطأ، ي
ّ
فمن ومن الشيطان.
إن أصبت فمن هللا ،وإن أخطأت ي
ّ
هذا العمل مقدم ثوابه لروح والدي ( قـ ــاسـ ــم مـ ـ ـفـ ـ ـي ـ ــد عـ ـ ـم ـ ــرو )
ُ َ
فمن وجد فيه فائدة فليدع له بالرحمة والمغفرة.
Database Systems Lab
Mid Summary
• Datatypes:
Data Type Description
VARCHAR2(size) Variable length character data
CHAR(size ) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
• Creating Table e.g.:
CREATE TABLE emp (
emp_name VARCHAR(20),
emp_id NUMBER(4),
CONSTRAINT emp_emp_id PRIMARY KEY (emp_name) );
• Creating Table using Subquery e.g.:
CREATE TABLE dept 80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
Database Systems Lab
Mid Summary
• Default Option e.g.:
CREATE TABLE emp (
emp_id NUMBER(4),
emd_hire_date DATE DEFAULT SYSDATE,
CONSTRAINT emp_emp_id PRIMARY KEY (emp_name) );
• Constraints:
Constraint Description
NOT NULL Ensures a column cannot have a NULL value.
UNIQUE Ensures all values in a column are unique across the
table.
PRIMARY KEY A combination of NOT NULL and UNIQUE.
FOREIGN KEY Ensures referential integrity by linking a column to a
PRIMARY KEY in another table.
CHECK Ensures that values in a column meet a specified
condition. (e.g., age >= 18).
Database Systems Lab
Mid Summary
• Alter/Modify Table:
Altering Type Exapmle
ADD COLUMN ALTER TABLE Customers
ADD Email VARCHAR2(255);
DROP COLUMN ALTER TABLE Customers
DROP COLUMN Email;
ALTER/MODIFY COLUMN ALTER TABLE Persons
Modify DateOfBirth date;
• Dropping Table e.g.:
DROP TABLE emp;
• Inserting new rows into table e.g.:
INSERT INTO departments
(department_id, department_name, manager_id, location_id) // Optional
VALUES
(70, 'Public Relations', 100, 1700);
• Inserting specific Date value e.g.:
INSERT INTO employees
VALUES ( 114 , 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3 , 1999 ', 'MON DD , YYYY'),
'AC_ACCOUNT', 11000 , NULL, 100 , 30 );
Database Systems Lab
Mid Summary
• Creating a script (input user “:”) e.g.:
INSERT INTO departments
(department_id, department_name, location_id)
VALUES ( :department_id, :department_name, :location_id);
• Updating rows in a table e.g.:
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
• Deleting rows from a table e.g.:
DELETE FROM departments
WHERE department_name = 'Finance';
• Select statements e.g.:
SELECT *
FROM emp;
SELECT emp_name, emp_id
FROM emp;
SELECT emp_name NAME, emp_id, sal*12 “Annual Salary”
FROM emp;
SELECT emp_name || ‘ Working with ID = ‘|| emp_id
FROM emp;
Database Systems Lab
Mid Summary
• Comparison Conditions:
Operator Meaning
= 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
LIKE Match a character pattern
IS NULL Is a null value
• Ordering rows ASC(default),DESC e.g.:
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ; // Or ORDER BY hire_date ASC ;
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
Database Systems Lab
Mid Summary
• Select +Where statements e.g.:
SELECT *
FROM emp
WHERE emp_id>10;
SELECT emp_name, emp_id
FROM emp
WHERE emp_name LIKE ‘%O’; // Last character is O
// % denotes zero or many characters.
// _ denotes one character.
SELECT emp_name NAME, emp_id, sal*12 “Annual Salary”
FROM emp
WHERE dept_no = 10; // department id = 10
SELECT emp_name || ‘ Working with ID = ‘|| emp_id
FROM emp
WHERE sal BETWEEN 1000 AND 5000; // Min sal=1000, Max sal = 5000
SELECT emp_name || ‘ Working with ID = ‘|| emp_id
FROM emp
WHERE emp_id IN (10,20,30); // Emp. In dept 10 or 20 or 30
SELECT emp_name NAME, emp_id, sal*12 “Annual Salary”
FROM emp
WHERE dept_no = 10 AND sal>5000; // department id = 10 and sal> 5000
SELECT emp_name NAME, emp_id, sal*12 “Annual Salary”
FROM emp
WHERE dept_no = 10 OR sal>5000; // department id = 10 or sal> 5000
SELECT emp_name || ‘ Working with ID = ‘|| emp_id
FROM emp
WHERE sal NOT BETWEEN 100 AND 500; // less than 100, more than 500
Database Systems Lab
Mid Summary
• Join e.g.:
SELECT department_id, department_name, location_id, city
FROM departments
NATURAL JOIN locations;
SELECT department_id, department_name, location_id, city
FROM departments JOIN locations
USING (department_id);
SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id);
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
