Revision Mid 496
Chapter 1
• Primary Key valves must be unique and not null.
• Candidate key =Any column that could be used as the PK. unique and does not change
• If no alternate data available to uniquely identify a person, create a surrogate key.A
surrogate key is a column that you create to be the record’s PK identi er.
• A FK is a column in a table that is a primary key in another table. it stores redundant
data.
• A composite key usually comprises elds that are primary keys in other tables.
• Normalization purpose is to eliminate data [Link] data does not have
a primary key.
• 1NF means that the data has been organized in such a manner that it has a primary key
and no repeating group.
• 2NF It has no partial dependencies and 1NF.
• 3NF it does not have any transitive dependences and 2Nf
• transitive dependency: a eld is dependent on eld within the table that not the table's
primary key .
fi
fi
fi
fi
Revision Mid 496
Chapter2
• (DDL) create new DB [Link] immediately change the DB, so you don’t need
to save the change explicitly
• (DML)To insert, update, delete, and view DB [Link] explicitly saved
• CREATE TABLE SQL syntax
CREATE TABLE tablename
(columnname1 data_type[constraints]
columnname2 data_type [constarints])
• Data type:
columnname VARCHAR2(maximum_size)
columnname CHAR[(maximum_size)]
columnname NUMBER [([precision,] [scale])]
columnname NUMBER(precision)
columnname DATE
• Constraints:
1-CONSTRAINT constraint_name PRIMARY KEY
2-Syntax (within a column declaration)(for foreign key)
CONSTRAINT constraint_name
REFERENCES primary_key_tablename (primary key_columnname)
2-Syntax (at end of column de nition)
CONSTRAINT constraint_name
FOREIGN KEY (columnname)
REFERENCES primary_key_tablename (primary key_columnname)
3-Check condition:
CONSTRAINT course_credits_cc
CHECK((credits > 0) AND (credits < 12))
CONSTRAINT term_term_desc_uk UNIQUE (term_desc)
• Table delete
Drop TABLE tablename
• Rename table
RENAME old_tablename TO new_tablename;
• Add new column to table
ALTER TABLE tablename
ADD(columnname data_declaration constraints);
• Deleting a Column
ALTER TABLE tablename
DROP COLUMN columnname;
• Renaming a Column
ALTER TABLE tablename
RENAME COLUMN old_columnname TO new_columnname;
fi
Revision Mid 496
Revision Mid 496
[Link] SQL for creation of table [Link] sure that the department
name is unique and the location must be entered.
CREATE TABLE Departments(
Department_id NUMBER(3) CONSTRAINT Depart_Pk PRIMARY KEY,
Department_name VARCHAR2(30) UNIQUE,
Manager_id NUMBER(7) CONSTARINT Manag_ID REFERENCES
Employees(Employee_id),
Location_id NUMBER(3) CONSTRAINT Loc_id REFERENCES
Loactions(Location_id) NOT NULL);
[Link] new column AssistantID to DEPARTMENTS [Link] sure that an assistant
is an employeee.
ALTER TABLE Departments
ADD AssistantID NUMBER(7) REFERENCES
Employee(Employee_id));
[Link] SQL to create table for JOBS and JOBS HISTORY
JOBS=
CREATE TABLE Jobs(
Job_id Varchar2(3) CONSTRAINT job_pk PRIMARY KEY,
Job_title Varchar2(40),
Min_salary NUMBER(8,3),
Max_salary NUMBER(8,3), )
JOBS HISTORY=
CREATE TABLE Job_history(
Employee_id Varchar2(3) CONSTRAINT e_Pk REFERNCES
Employees(Employee_ID),
Start_date DATE,
End_date DATE,
Job_id Varchar2(3) CONSTARINTS job_fk REFERNCES
Jobs(Job_id),
Department_id Varchar2(3) dep_fk REFERNCES
Department(Department_id));
Revision Mid 496
Chapter3
• Inserting values into every column:
INSERT INTO tablename
VALUES (column1_value, column2_value, ... );
• COMMIT (save) all of the changes or ROLL BACK (discard) all changes
• Search Conditions
WHERE columnname comparison_operator search_expression
• Updating Table Rows
UPDATE tablename
SET column1 = newvalue1, column2 = newvalue2,
WHERE search condition;
• Deleting Table Rows
DELETE FROM tablename
WHERE search condition;
• Retrieving Data From a Single Table
SELECT columnname1, columnname2, ...
FROM [Link]
[WHERE search_condition];
• Retrieve all of columns
SELECT * FROM location
To get rid of duplicate values in your results, use the DISTINCT keyword.
SELECT DISTINCT columnname FROM tablename;
To nd NULL values:
WHERE columnname IS NULL
To nd NOT NULL values:
WHERE columnname IS NOT NULL
To sort query results, use the ORDER BY clause.
SELECT columnname1, columnname2, …
FROM [Link]
WHERE search_condition
ORDER BY sort_key_column;
fi
fi
Revision Mid 496
• Descending Order: Use DESC for reverse order.
ORDER BY columnname DESC;
• To group rows with duplicate values and apply aggregate functions, use GROUP
BY.
SELECT column1, aggregate_function(column2) FROM table_name
GROUP BY group_columnname;
• An alias gives an alternate name to a column, which can be used in the query.
SELECT columnname1 AS alias_name
FROM table_name;
• Joins combine tables based on foreign key references.
SELECT column1, column2, ...
FROM table1, table2
WHERE [Link] = [Link] AND search_condition(s);
• A nested query is a query within another query. It consists of:
SELECT eldname1, eldname2, ...
FROM table1, table2, ...
WHERE join conditions
AND search_ eld1 = (SELECT eldname1
FROM table1, table2, ...
WHERE search and join conditions)
• When you create a query that joins a table to it self, you create a self-join. To create a
self-join, you must create a table alias
• UNION suppresses duplicates, and shows duplicate row only once, while UNION
ALL displays all duplicate rows.
• MINUS automatically suppressed duplicate rows.
How to Create a View:
CREATE VIEW view_name AS source_query;
To replace an existing view:
CREATE OR REPLACE VIEW view_name AS source_query;
Updatable Views
To add a faculty member:
INSERT INTO faculty_view VALUES (6, 'May', 'Lis', 'I', 11, '7155552508', 'INST');
To remove a faculty member:
DELETE FROM faculty_view WHERE f_last = 'May';
Joining Views:
SELECT [Link], l.building_code, l.room_number FROM FACULTY_VIEW f
JOIN LOCATION l ON f.location_id = [Link];
Removing a View:
DROP VIEW view_name;
fi
fi
fi
fi
Revision Mid 496
Revision Mid 496
Revision Mid 496
1)Write query to get productlist(id, name, unit price) where product cost between $15 and $25
Select ProductID,ProductName,UnitPrice
From Products
Where UnitPrice >=15 AND UnitPrice <=25;
2)Write query to get productlist(name, unit price)of products above average price
Select ProductName,UnitPrice
From Products
Where UnitPrice > (Select avg(UnitPrice)
From Products)
3)write query to list all customers (customers,company name, address)that ordered a product
supplied from supplier based in bahrain.
Select CustomerID,CompangName,Address
From customers cu ,order o, products suppliers s
Where [Link]=[Link] AND
[Link]=[Link] AND
[Link]=[Link] AND
[Link]=‘bahrain’;
4)determine which customers have purchased the same products as customer#3
Select CustomerID
From orders o ,orders details od
Where [Link] =[Link] AND
Productid IN (Select Productid
From orders o ,order details od
Where [Link] =[Link] AND
Customerid=3)
5)display customer details(customers,companyname,addresss)of customer who place order#1 and
total amount of order(unit price*quantity)
Select CustomerID,ComapnyName,address,sum(unit price*quantity)
From customers, orders o ,order details od
Where [Link]=[Link] AND
[Link]=[Link] AND
[Link]=1
Group by CustomerId;
Revision Mid 496
Chapter SQL/PL
Basic structure
[DECLARE] BEGIN --statements [EXCEPTION] END;
To print
BEGIN
DBMS_OUTPUT.PUT_LINE('PL/SQL is easy!'); END;
Create a variable to store today's date
DECLARE
v_date DATE := SYSDATE;
Intialize variable
DECLARE
v_counter INTEGER := 0;
BEGIN
v_counter:= v_counter + 1; DBMS_OUTPUT.PUT_LINE(v_counter); END;
Declare a variable to hold a complete row from the employees table
DECLARE
v_emp_record employees%ROWTYPE;
If statement in boolean
DECLARE
v_valid1 BOOLEAN := TRUE;
v_valid2 BOOLEAN;
v_valid3 BOOLEAN NOT NULL := FALSE; BEGIN
IF v_valid1 THEN
DBMS_OUTPUT.PUT_LINE('Test is TRUE'); -- Print if TRUE
ELSE
DBMS_OUTPUT.PUT_LINE('Test is FALSE'); -- Print if FALSE
END IF; END;
Using %TYPE ensures your variables match the data types of table columns
identi er table_name.column_name%TYPE;
Inserting Data
The INSERT statement adds new row(s) to a table.
BEGIN
INSERT INTO copy_emp
(employee_id, rst_name, last_name, email, hire_date, job_id, salary) VALUES (99, 'Ruth',
'Cores', 'RCORES', SYSDATE, 'AD_ASST', 4000);
END;
Update data
DECLARE
v_sal_increase [Link]%TYPE := 800;
BEGIN
UPDATE copy_emp -- Start the update
SET salary = salary + v_sal_increase -- Increase salary by 800
WHERE job_id = 'ST_CLERK'; -- Only for employees who are stock clerks
fi
fi
Revision Mid 496
END;
Delet data
DECLARE
v_deptno employees.department_id%TYPE := 10; -- Set the department number to
delete BEGIN
DELETE FROM copy_emp -- Start the delete operation
WHERE department_id = v_deptno; -- Remove rows for department 10 END;
If statement
IF condition THEN statements;
ELSIF condition THEN statements;
ELSE statements;
END IF;
Loop
BEGIN LOOP
statements;
EXIT WHEN condition; END LOOP;
END;
While loop
WHILE condition LOOP statement1; statement2;
-- ...
END LOOP;
For loop
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1;
statement2;
-- ...
END LOOP;
Revision Mid 496
