π Lecture 16 β Using Data Definition Language (DDL) in Oracle 11g
π Overview: This lecture introduces Data Definition Language (DDL) in Oracle 11g, focusing on how to create, alter, and drop database objects like tables. Understanding DDL is essential because it forms the foundation for storing and retrieving data in any relational database system.
ποΈ Topics Covered
The lecture covers the general syntax of the CREATE TABLE statement, creating a single table using an ERD example, creating tables with foreign keys and composite primary keys, the ALTER TABLE command for adding columns or modifying data types, and the DROP TABLE command with proper table dropping sequence to maintain referential integrity.
π Lecture Summary
A. Syntax of DDL
Data Definition Language (DDL) is used to create database objects in the Database. Every database object has a Data Dictionary created and managed by the System. The most important database object is the Table, which is required for storage and retrieval of data. DDL includes Create, Alter, and Drop statements.
The general syntax of the CREATE TABLE statement is:
Create Table TableName (Column datatype [null | not null], ....)
Common data types include: i. Number β for numeric values ii. VarChar2 β Variable Length Character (stores text) iii. Date β stores dates in DD/Mon/YYYY format
π Definition β DDL (Data Definition Language): A subset of SQL used to define and manage database structures, including creating, altering, and dropping database objects.
π Formula/General Syntax:
Create Table TableName (Column datatype [null | not null], ....)
β This creates a new table with specified column names, their data types, and optional null/not null constraints.
B. Creating Single Table
The lecture provides an example ERD (Entity Relationship Diagram) showing the "Building" table. This table becomes a single table implementation in the database.
π‘ Why this matters: Creating single tables is the first step in database implementation β each entity from an ERD typically becomes one table.
π Example: DDL statement to implement the Building Table:
Create table Building (
bname varchar2(30) primary key,
address varchar2(40),
phone number number(10)
);
This creates a table named "Building" with three columns: bname (set as the primary key), address, and phone number.
C. Creating Table with Foreign Key
In the Floor table, bname is a foreign key that references the Building table. Floor# and bname together form a Composite Primary Key for the Floor table.
π Definition β Composite Primary Key: A primary key that consists of two or more columns combined to uniquely identify each row in a table.
π Definition β Foreign Key: A column or set of columns in one table that references the primary key of another table, maintaining referential integrity.
π Example: Code to implement the Floor table with foreign key and composite primary key:
Create table Floor (
floorno number(10),
no_of_apt number(10),
bname varchar2(30) references building(bname),
primary key (floorno, bname)
);
This creates the Floor table where bname references the Building table's primary key, and the combination of floorno and bname serves as the composite primary key.
D. Alter Table Command
The ALTER TABLE command is used to either add a new column or modify an existing column's data type or size.
π Example 1: Adding a new column "rent" to the Building table:
Alter table Building add (rent number(10));
π Example 2: Modifying the data type of "phoneno" from number to varchar2:
Alter table Building modify (phoneno varchar2(10));
π Definition β ALTER TABLE: A DDL command used to modify the structure of an existing table by adding, modifying, or dropping columns.
E. Drop Table Command
To drop a table from the database, the DROP TABLE command is used. It is critical to maintain the correct sequence when dropping tables β all child tables must be dropped first, and then the parent table can be dropped.
π Definition β Parent Table: A table whose primary key is referenced by a foreign key in another (child) table. π Definition β Child Table: A table that contains a foreign key referencing another (parent) table's primary key.
π Example: Correct sequence to drop tables:
Drop table floor; -- Child table dropped first
Drop table building; -- Parent table dropped second
π‘ Why this matters: Dropping a parent table before its child table would violate referential integrity constraints and cause errors. Always drop child tables first.
β Key Takeaways
The most critical concepts from this lecture are: DDL consists of CREATE, ALTER, and DROP statements for managing database objects. The CREATE TABLE command requires specifying column names, data types (NUMBER, VARCHAR2, DATE), and constraints like PRIMARY KEY and FOREIGN KEY. Composite primary keys are formed using multiple columns, while foreign keys maintain relationships between tables. ALTER TABLE allows adding new columns or modifying existing column properties. When dropping tables, always drop child tables (those with foreign keys) before parent tables to avoid referential integrity errors.
π§ Quick Revision Questions
- What are the three main DDL commands discussed in this lecture?
- What is the difference between a simple primary key and a composite primary key?
- In the Floor table example, why is "bname" both a foreign key and part of the composite primary key?
- What is the correct sequence for dropping the Building and Floor tables, and why?
- How would you add a new column called "construction_year" with a DATE data type to the Building table?
π Lecture 17 β Using Data Manipulation Language (DML) and Data Control Language (DCL)
π Overview: This lecture introduces the fundamental Data Manipulation Language (DML) commands β INSERT, UPDATE, DELETE, and TRUNCATE β used to manage data within database tables. It also covers Data Control Language (DCL) concepts through transaction control using COMMIT and ROLLBACK statements, which are essential for maintaining data integrity.
ποΈ Topics Covered
The lecture covers the Insert Statement syntax for adding data to tables, including limited column insertion; the Update Table syntax for modifying single or multiple columns with conditions; the Delete Statement for removing rows; the Truncate Table command for faster removal of all rows; and Transaction basics including Commit and Rollback statements for saving or undoing database changes.
π Lecture Summary
A. Insert Statement Syntax
The insert statement is used to add data into one or more tables in a database. Every column in the list-of-columns must have a valid value in the corresponding position in the list-of-values. Before inserting a row, you must know the table's columns and their valid values. Referential Integrity constraints are implemented by default.
π Definition β Insert Statement: A DML command used to add new rows of data to a table.
π Syntax: INSERT into TableName VALUES (value 1, value 2...); β Adds a complete row with values for all columns.
π Syntax (with columns): INSERT into TableName (column1, column2) VALUES (value1, value2); β Adds values only to specified columns.
π Example: INSERT into building VALUES ('Test', 'VU', 3948283); inserts a new building record. INSERT into building (bname, address) VALUES ('Test-1', 'VU -LRO'); inserts only building name and address.
KKKKKK. Viewing Data from Table
To verify the insertion of data in a table, the following query can be used: Select * from building;. Detailed discussion on the SELECT statement will be covered later in the course.
LLLLLL. Limited Column Data Insertion
If values for only a limited number of columns are known, then limited values can be added to the table. However, the value of the Primary Key column cannot be left NULL.
MMMMMM. Update Table Syntax
The Update command is used to modify one or more columns of a table. Each value must be valid for its column-name. If the WHERE clause is included, the statement updates column values only in rows that satisfy the condition.
π Definition β Update Statement: A DML command used to modify existing records in a table.
π Syntax: UPDATE TableName SET column1 = value, column2 = value WHERE condition; β Changes specified column values in rows matching the condition.
π‘ Why this matters: Without a WHERE clause, ALL rows in the table will be updated, which can cause unintended data changes.
NNNNNN. Implementing Single Column Update Statement
π Example: UPDATE emp SET ename='Anders' WHERE empno=7369; changes the employee name to "Anders" for employee number 7369. Verification: SELECT * from emp;
OOOOOO. Implementing Multiple Column Update Statement
π Example: UPDATE emp SET job='MANAGER', sal=2000 WHERE empno=7369; updates both the job title and salary columns for employee number 7369. Verification: SELECT * from emp;
PPPPPP. Delete Statement Syntax
The Delete statement is used to remove one or more rows from a table. The deleted rows can be rolled back if not committed. Being committed means all changes in the database have been made permanent using the Commit command. The Rollback command is used to restore changes made in the database to the way they were before (this only works if you haven't already used Commit). Delete command can also be used with a condition to delete a particular row.
π Definition β Delete Statement: A DML command that removes rows from a table, with the possibility of rollback if not committed.
π Syntax (all rows): DELETE FROM TableName; β Removes all rows from the table.
π Syntax (specific rows): DELETE FROM TableName WHERE condition; β Removes only rows satisfying the condition.
QQQQQQ. Implementing Delete Statement
π Example: DELETE FROM emp WHERE empno=7369; deletes the employee with employee number 7369. DELETE FROM emp; will delete all rows from the table emp.
RRRRRR. Truncate Table
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers and other dependencies.
π Definition β Truncate: A DDL command that removes all rows from a table while preserving the table structure, faster than DELETE but cannot be rolled back.
SSSSSS. Transaction Basics
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether manually by a user or automatically by a database program. A transaction is the propagation of one or more changes to the database. For example, creating, updating, or deleting a record from a table means you are performing a transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
TTTTTT. Commit Statement
The Commit Statement is used to save the changes made in the database. It is the transactional command used to save changes invoked by a transaction to the database. The Commit Statement saves all transactions to the database since the last Commit or Rollback Statement. The DML statements are not auto-commit and should be saved by giving the commit statement.
π Definition β Commit: A transaction control command that makes all changes permanent and visible to other users.
UUUUUU. Rollback Statement
The Rollback Statement is the transactional command used to undo transactions that have not already been saved to the database. The Rollback Statement can only be used to undo transactions since the last Commit Statement or Rollback Statement was issued. In simple words, it is used to undo the work performed by the current transaction.
π Definition β Rollback: A transaction control command that reverts the database to its state before the last Commit or Rollback statement.
β Key Takeaways
The four core DML commands covered are INSERT, UPDATE, DELETE, and TRUNCATE β with INSERT requiring valid values for each column and never allowing NULL in a Primary Key; UPDATE allowing single or multiple column modifications based on a condition; DELETE being rollback-able if not committed; and TRUNCATE being faster but irreversible. Transaction control is managed through COMMIT (permanently saves changes) and ROLLBACK (undoes uncommitted changes), with the critical rule that DML statements are not auto-committed and require an explicit COMMIT to make changes permanent. Finally, without a WHERE clause, UPDATE and DELETE commands affect all rows in the table.
π§ Quick Revision Questions
- What is the difference between the DELETE and TRUNCATE commands in terms of rollback capability and speed?
- What happens if you execute an UPDATE statement without a WHERE clause?
- Can you insert a NULL value into a Primary Key column during limited column data insertion?
- What is the purpose of the COMMIT statement, and why is it necessary after DML operations?
- Explain what the ROLLBACK statement does and under what conditions it can be used to undo changes.
π Lecture 18 β Structured Query Language (SQL) Basics
π Overview: This lecture introduces Structured Query Language (SQL), the standard language for managing and retrieving data in relational databases. It covers fundamental SQL syntax, filtering with WHERE clause, logical operators, pattern matching with LIKE and wildcards, and advanced query capabilities including GROUP BY, HAVING, and ORDER BY clauses.
ποΈ Topics Covered
SQL basics including DDL, DML, DRL, and DCL components; SQL syntax with SELECT, DISTINCT, and WHERE clause implementation; logical operators (AND, NOT, OR) with truth tables; wildcard characters (%) and (_) with LIKE operator for pattern matching; IN and BETWEEN operators; single-row functions (ROUND, TRUNC, LENGTH, INSTR, CONCAT); multiple-row/group functions (COUNT, SUM, MIN, MAX, AVG); GROUP BY clause for grouping data; HAVING clause for filtering grouped results; ORDER BY clause for sorting; date handling and date functions.
π Lecture Summary
A. Structured Query Language (SQL)
SQL is a database computer language designed for the retrieval and management of data in relational databases. SQL stands for Structured Query Language. SQL consists of a data definition language (DDL) , data manipulation language (DML) , data retrieval language (DRL) and a data control language (DCL) . The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control.
XXX. SQL Syntax
All SQL statements start with keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all statements end with a semicolon (;) .
SELECT DISTINCT * ColumnName FROM TableName;
-
- means all the columns
- ColumnName is one or more column from table
- DISTINCT means unique values from column
WWWWWW. Implementing SQL
Sample SQL statements where capital words are SQL commands and words in capital letters are table names:
SELECT ename, job FROM emp WHERE job ='MANAGER';
SELECT ename, sal FROM emp WHERE sal<> 2000;
XXXXXX. SQL and WHERE Clause
The WHERE clause includes a condition that restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set where the condition does not evaluate to True. From a large table, required rows can be fetched by using WHERE clause which applies to each row of the table. If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetch only necessary records.
YYYYYY. Implementing SQL and WHERE Clause
Example 01: Get list of all employees who are titled as managers:
SELECT ename, job FROM emp WHERE job ='MANAGER';
Example 02: Get list of all employees whose salary is not equal to 2000:
SELECT ename, sal FROM emp WHERE sal<> 2000;
ZZZZZZ. SQL and Logical Operators
Logical operators are used to specify multiple conditions in a single WHERE clause to retrieve rows based on values in multiple columns. AND, NOT and OR operators combine two or more conditions into a compound condition to get specific required results.
| Logical Operator | Description | Syntax |
|---|---|---|
| AND | Returns TRUE if both conditions are TRUE | SELECT * FROM EMP WHERE job='CLERK' AND deptno=10 |
| NOT | Reverses the value of the condition | SELECT * FROM EMP WHERE NOT (job IS NULL) |
| OR | Returns TRUE if either condition is TRUE | SELECT * FROM emp WHERE job='CLERK' OR deptno=10 |
Truth Table:
| Condition 01 | Condition 02 | OR | AND |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | TRUE | FALSE |
| FALSE | TRUE | TRUE | FALSE |
| FALSE | FALSE | FALSE | FALSE |
AAAAAAA. Implementing Logical Operator 01
Scenario: Find list of all employee names earning more than 2500 but less than 5000.
SELECT ename, sal FROM emp WHERE sal> 2500 AND sal< 5000;
BBBBBBB. Implementing Logical Operator 02
Scenario: Find all employees working in Dept # 20 with designation of Analyst.
SELECT * FROM emp WHERE deptno=10 AND job='ANALYST';
CCCCCCC. Implementing Logical Operator 02
Scenario 01: Display list of all empno earning more than 400 as commission with designation of Manager.
SELECT empno FROM emp WHERE comm> 400 and JOB='MANAGER';
Scenario 02: Find all salesmen working in dept # 30 with salary less than 1500.
SELECT * from emp WHERE JOB='SALESMAN' AND deptno=30 and sal< 1500;
DDDDDDD. Wildcard Characteristics in SQL
Two wildcard characters are used to broaden selections: the percent sign (%) and the underscore (_) . The percent sign allows substitution of one or more characters in a field. The underscore allows substitution of a single character in an expression. Wildcards are used when part of the value to be searched is known, not the exact value.
- %: Zero or more characters
- _ : Exactly one character
EEEEEEE. Like Operator in SQL
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Wildcard operators (% and _) identify the patterns. The SQL LIKE condition allows you to use wildcards to perform pattern matching in the WHERE clause of SELECT, INSERT, UPDATE, or DELETE statements.
FFFFFF. LIKE Syntax
Basic syntax of LIKE clause:
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern and column_name LIKE pattern;
LIKE with Percentage (%):
SELECT FROM table_name WHERE column LIKE 'XXXX%'
LIKE with Underscore (_):
SELECT FROM table_name WHERE column LIKE 'XXXX_'
GGGGGGG. Implementing Like β 01
Scenario: Display list of names of all employees who have either E in the name or the name should end with G with at least two characters.
SELECT * FROM emp WHERE ename LIKE'%E%' OR ename LIKE'%-G';
HHHHHHH. Implementing Like β 02
Scenario: Display all information about employees having RE in the job with at least three characters and earning at least 2500 but at most 5000.
Select * FROM emp WHERE job LIKE'%RE-%' AND sal> 2500 AND sal< 5000;
Practice Scenarios:
- Write a query to display all info about employees with at least one vowel in the ename
- Write a query to display list of employees earning more than 2000 but at most 5998 and having at least two occurrences of E in the ename
IIIIIII. IN Operator
The IN operator works like the OR Operator, allowing search for a value from a given list of values.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
JJJJJJJ. Implementing IN Operator
Scenario: Display all information about employees working as MANAGER, ANALYST, or CLERK, belonging to department number 20, earning at most 2500.
SELECT * FROM emp WHERE job IN ('MANAGER', 'ANALYST', 'CLERK') AND deptno = 20 AND sal<=2500;
KKKKKKK. BETWEEN Operator
The BETWEEN operator selects values within a range; values can be numbers, text, or dates. The border values of the range are included in the search. The BETWEEN operator takes minimum and maximum values and searches between the two including the min and max values.
Select * from table_name Where col_name BETWEEN val1 AND val2
LLLLLLL. Implementing BETWEEN Operator
Scenario: Display all information about employees earning at least 2975 and at most 5000, working in department 20, reporting to employee with empno 7839.
SELECT * FROM emp WHERE sal BETWEEN 2975 AND 5000 AND deptno= 20 AND mgr = 7839
MMMMMMM. Single Row Function
Single row functions operate on single rows only and return one result per row. They accept one or more arguments and return one value for each row returned by the query. Single row functions can be character functions, numeric functions, date functions, and conversion functions. They can be used in SELECT and WHERE statements.
NNNNNNN. Implementing Single Row Function
ROUND and TRUNC:
SELECT ROUND(194.683,1), TRUNC(194.683,1) FROM dual;
LENGTH, INSTR & CONCAT:
SELECT LENGTH(ename), INSTR(ename, 'E'), CONCAT(ename,job) FROM emp;
π‘ Why this matters: Single row functions allow you to transform and manipulate individual data values row by row, which is essential for data cleaning and formatting operations.
OOOOOOO. Group or Multiple Row Function
Group functions manipulate groups of rows to give one result per group of rows. They compute an aggregate value based on a group of rows.
SELECT COUNT *, sum(sal), min(sal), max(sal), Avg(sal) FROM emp;
PPPPPPP. GROUP BY Clause
GROUP BY clause groups similar rows together to form groups, and the multiple row function is used with the GROUP BY clause. The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
SELECT column1, column2 FROM table_name WHERE [conditions] GROUP BY column1, column2
QQQQQQQ. Implementing GROUP BY Clause 01
Situation: What is the salary paid of each department?
- Need to group together all rows of each department separately
- Need sum of salary for each department group
SELECT SUM(sal), deptno FROM emp GROUP BY deptno;
RRRRRRR. Implementing GROUP BY Clause 02
Scenario: What is average and maximum salary paid to each Job who are reporting to MGR 7839?
SELECT avg(sal), max(sal), job FROM emp WHERE mgr=7839 GROUP BY job
Note: Error occurs if job, mgr are not written after GROUP BY clause.
SSSSSSS. HAVING Clause
The HAVING clause is like the WHERE clause but filters results in aggregated/grouped data. The WHERE clause cannot be used in aggregated data, so SQL HAVING clause filters the results. The WHERE clause places conditions on selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
SELECT column1, column2 FROM table1, table2 GROUP BY column1, column2 HAVING [conditions]
TTTTTTT. Implementing HAVING Clause β 1
Scenario: Display average salary of each department if there are at least 2 employees working in the department.
- Group together rows of each department
- Calculate average salary for each department group
- Count number of employees in each group
- Display only groups with more than 2 rows
SELECT avg(sal) FROM emp GROUP BY deptno HAVING count(*) > 3;
UUUUUUU. Implementing HAVING Clause β 2
Scenario: Display maximum and minimum salary by each department if average salary is more than 1500 and less than 3000. Exclude employees with any occurrence of 'A' in the ename.
SELECT max(sal), min(sal) FROM emp WHERE ename NOT LIKE'%A%' GROUP BY deptno HAVING avg(sal) > 1500 AND avg(sal) < 3000;
VVVVVVV. ORDER BY Clause
The ORDER BY clause is used with the SQL SELECT statement to sort results in ascending or descending order. You specify one or more columns for sorting. The ORDER BY keyword sorts records in ascending order by default. Use the DESC keyword to sort in descending order.
SELECT column_name, FROM table_name ORDER BY column_name ASC|DESC
WWWWWWW. Date Handling
Date is format specific in SQL and uses a specific format (MM-DD-YY). Two dates cannot be added, subtracted, multiplied, or divided. Days can only be added to Date.
SELECT hiredate + 30, hiredate-50, sysdate, comm, sal, deptno FROM emp WHERE deptno=30 AND comm IS NOT null OR sal< 3000;
XXXXXXX. Date Functions
MONTHS_BETWEEN: Returns the number of months between date1 and date2
MONTHS_BETWEEN( date1, date2 )
ADD_MONTHS: Returns a date with a specified number of months added
ADD_MONTHS( date1, number_months )
NEXT_DAY: Returns the first weekday that is greater than a date
NEXT_DAY( date, weekday )
LAST_DAY: Returns the last day of the month based on a date value
LAST_DAY( date )
Example:
SELECT MONTHS_BETWEEN (sysdate, hiredate)/12, ADD_MONTHS (sysdate, hiredate), NEXT_DAY (sysdate, 'Wednesday'), LAST_DAY (sysdate) FROM emp;
β Key Takeaways
SQL is a comprehensive language with four components: DDL, DML, DRL, and DCL, and all statements end with a semicolon. The WHERE clause filters rows based on conditions, and logical operators (AND, OR, NOT) combine multiple conditions with specific truth table behavior. Pattern matching uses LIKE with % (zero or more characters) and _ (exactly one character) wildcards, while IN and BETWEEN operators provide alternative filtering methods. Group functions (COUNT, SUM, MIN, MAX, AVG) work with GROUP BY to aggregate data, and the HAVING clause filters these aggregated groups (unlike WHERE which filters individual rows). Single-row functions operate per row while group/multiple-row functions operate on groups, and ORDER BY sorts results ascending by default or descending with DESC keyword.
π§ Quick Revision Questions
-
What is the difference between WHERE and HAVING clauses in SQL, and when would you use each?
-
How does the LIKE operator with % wildcard differ from LIKE with _ wildcard? Provide an example of each.
-
What is the purpose of the DISTINCT keyword in a SELECT statement, and what would
SELECT DISTINCT deptno FROM emp;return? -
Explain the behavior of AND vs OR logical operators when one condition is TRUE and the other is FALSE.
-
What does the GROUP BY clause do, and why can't the WHERE clause be used to filter grouped data?
π Lecture 19 β Advance SQL
π Overview: This lecture covers advanced SQL techniques for combining data from multiple tables. It explores Cartesian products, various types of joins (inner, self, outer), and subqueries, explaining how each operation works and when to use them to retrieve meaningful information from relational databases.
ποΈ Topics Covered
This lecture begins with the mathematical concept of Cartesian products and demonstrates their application in SQL, including the pitfalls of using them without proper join conditions. It then moves to Inner Joins, explaining how they combine tables using common keys. The lecture continues with Self-Joins for recursive relationships and Outer Joins (Left and Right) to retain non-matching rows. Finally, it introduces Subqueries as an alternative to joins for dynamic criteria-based data selection.
π Lecture Summary
A. Cartesian Product
Cartesian product is mathematically a binary operation where two objects or sets (or tables) are combined in an βeverything in combination with everythingβ fashion. In an SQL statement, a Cartesian product is where every row of the first table is joined with every row of the second table. Simply defining, the pairing of one element in a set with every element of the second set is called a Cartesian product.
π Definition β Cartesian Product: A binary operation that pairs each element of one set with every element of another set, resulting in all possible combinations. π Formula: Total Number of Pairs = No. of Elements in Set A * No. of Elements in Set B π Example: For Set A = {2, 5, 6} and Set B = {8, 1}
- A * B = {(2,8), (2,1), (5,8), (5,1), (6,8), (6,1)}
- Total Pairs = 3 * 2 = 6
YYYYYYY. Cartesian Product & Joins
As mentioned above, Cartesian product is the pairing of one element in one set with every element of the other set. But, as relational databases are usually normalized, combining two tables creates another table that combines information from both tables. This will result in duplication in the data, losing data integrity. Joining tables without a proper condition will show wrong data.
π Example: Employee Database with EMP table (Empno, Ename) and DEPT table (Deptno, Dname)
- EMP rows: (1, Imran), (2, Kashif), (3, Asif)
- DEPT rows: (10, Sales), (20, Marketing), (30, Engineering)
- Output of Cartesian Product: 3 * 3 = 9 rows
- Every employee appears to be working in every department, which is WRONG information.
π‘ Why this matters: A Cartesian product without a join condition produces meaningless, duplicated data that misrepresents real-world relationships.
ZZZZZZZ. Join or Inner Join
Joins are required when data from multiple tables is required. The standard join operation is known as an inner join. It horizontally combines two or more tables into a single working table. An inner join is performed by logically performing the Cartesian product (generating all combinations of rows) of the tables, then selecting only those rows that satisfy the join condition. A primary key field in one table can be a foreign key field in another table, and a join operation is used to combine tables using a common key in both tables.
π Definition β Inner Join: A join operation that returns only rows from both tables that satisfy the specified join condition.
π Example: Basic Join Statement
SELECT empno, ename, d.deptno, dname
FROM emp e, dept d
WHERE d.deptno = e.deptno;
π Example: Join with Other Conditions
SELECT empno, ename, d.deptno, dname,
ROUND(months_between(sysdate, hiredate),0), hiredate
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND months_between(sysdate, hiredate) > 410;
π Formula for Self-Join Syntax:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
AAAAAAAA. Self-Join
A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. In a self-join, a table is joined with itself, especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table to itself means that each row of the table is combined with itself and with every other row of the table. Self-joins are used in a recursive relationship.
π‘ Why this matters: Self-joins allow you to model hierarchical or recursive relationships like prerequisites (where a course's prerequisite must be a valid course itself), managers and employees, or parent-child relationships within a single table.
BBBBBBBB. Implementing Self-Joins
π Example 01: Find managers and their employees
SELECT e.ename, e.empno, b.ename, b.empno
FROM emp e, emp b
WHERE e.empno = b.mgr;
π Example 02: Consider a Customer Table with attributes: Customer (id, name, age, address, salary)
SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;
CCCCCCCC. Outer Join
An outer join extends the result of a simple join. An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each recordβeven if no other matching record exists, non-matching rows appear as NULL. The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition. Outer joins subdivide further into left outer joins and right outer joins, depending on which table's rows are retained.
π Definition β Outer Join: A join that returns all rows from one or both tables, filling non-matching rows with NULL values.
DDDDDDDD. Left Outer Join
A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table, including rows with NULL (empty) values in the link field. All the rows from the left table will be included and all the matching rows from the right table will be included. A NULL value will be displayed for a non-matching value. Simply defining, the result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
π Definition β Left Outer Join: A join that returns all rows from the left table, with matching rows from the right table, and NULLs for non-matching right table columns.
EEEEEEEE. Implementing Left Outer Join
Suppose a table called suppliers with two fields (supplier_id and supplier_name) and another table called orders with three fields (order_id, supplier_id, and order_date).
π Example: Left Outer Join SQL Statement
SELECT supplier.sid, supplier.sname, orders.doe
FROM supplier, orders
WHERE supplier.sid = orders.sid(+);
The (+) operator on the right side (orders.sid) indicates a left outer join, meaning all rows from the left table (supplier) are retained.
The result set includes rows for Microsoft and NVIDIA even if they have no matching orders, with the order_date field containing NULL for these two.
FFFFFFFF. Right Outer Join
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
π Definition β Right Outer Join: A join that returns all rows from the right table, with matching rows from the left table, and NULLs for non-matching left table columns.
GGGGGGGG. Implementing Right Outer Join
Consider two tables: Suppliers and Orders.
π Example: Right Outer Join SQL Statement
SELECT supplier.sid, supplier.sname, orders.doe
FROM supplier, orders
WHERE supplier.sid(+) = orders.sid;
The (+) operator on the left side (supplier.sid) indicates a right outer join, meaning all rows from the right table (orders) are retained.
The result includes the row for order_id 500127 even if it has no matching supplier, with the supplier_name field containing a null value.
HHHHHHHH. Subquery Basics
A subquery is a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time. An SQL subquery is usually added in the WHERE Clause of the SQL statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement but do not know the exact value in the database. Subqueries are an alternate way of returning data from multiple tables, or simply, an alternative of joins.
π Definition β Subquery: A nested query placed inside another SQL statement, used to dynamically determine criteria at runtime.
π Formula: General Syntax
SELECT *
FROM t1
WHERE column1 = (SELECT column1 FROM t2);
IIIIIIII. Implementing Subqueries
π Scenario 01: Write a query to display information of all those employees who are earning the minimum salary.
SELECT ename, sal, deptno
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);
π Scenario 02: Write a query to display all those deptno where the minimum salary is less than the average salary of all employees.
SELECT deptno, MIN(sal)
FROM employees
GROUP BY deptno
HAVING MIN(sal) < (SELECT AVG(sal) FROM employees);
β Key Takeaways
For the exam, you must understand that a Cartesian product generates all possible row combinations and is only useful when filtered with a proper join condition. Inner joins combine tables using a common key and return only matching rows. Self-joins allow a table to be compared with itself, useful for recursive relationships like manager-employee hierarchies. Outer joins (left and right) extend inner joins by retaining all rows from one side, filling non-matching fields with NULL. Finally, subqueries provide a dynamic way to filter data when exact values are unknown, serving as an alternative to joins for multi-table queries.
π§ Quick Revision Questions
- What is the mathematical result of a Cartesian product between two sets of sizes m and n?
- How does an inner join differ from a Cartesian product in terms of the rows it returns?
- In what type of database relationship would you use a self-join, and what is the basic syntax?
- What is the difference between a LEFT OUTER JOIN and a RIGHT OUTER JOIN regarding which table's rows are retained?
- Write a SQL subquery to find employees whose salary is greater than the average salary of all employees.
π Lecture 20 β Database Views and Data Dictionaries in Oracle 11g
π Overview: This lecture introduces SQL views as virtual tables that derive data from base tables, and explains their syntax, creation, and practical applications. It also covers data dictionaries β the read-only system tables that store metadata about the database β and how to use them for database management and administration.
ποΈ Topics Covered
The lecture covers the motivation for using views, the syntax for creating views, writing basic and complex views including the WITH CHECK OPTION constraint, data dictionary concepts and their primary uses, retrieving information from data dictionary views like ALL_TABLES and USER_CONSTRAINTS, and the relationship between data dictionaries and views.
π Lecture Summary
A. Motivation for Views
A view can be thought of as either a window of a table or a virtual table. A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based; the view does not have its own data. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is created by a pre-defined SQL statement that joins one or more tables.
Access to data can be improved using SQL views as they summarize data from various tables which can further be used to generate reports. Views are very powerful because they allow you to tailor the presentation of data to different types of users. While views can help to obscure large queries from users and to standardize data access, a view can also draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view, and the queries can be reused to reduce time. All DML statements can be issued against a view.
π‘ Why this matters: Views provide both security (by hiding sensitive columns) and simplicity (by pre-joining tables) β they are essential for building user-friendly database applications.
Syntax of a View
The following is the general syntax of a view:
π Definition β View Creation Syntax:
CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;
Writing Views
Suppose we have EMP and DEPT tables. To see the empno, ename, sal, deptno, department name and location, we would normally have to give a join query like this:
CREATE VIEW emp_det AS
SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
This SQL CREATE VIEW example creates a virtual table based on the result set of the SELECT statement. You can now query the SQL VIEW as follows:
SELECT * FROM emp_det;
This will show the same result as typing the long join query. Now you can treat this EMP_DET view the same as any other table.
Writing Complex Views
WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select. Consider the following example:
Suppose all employees working in Department No. 10 belong to the accounts department and most of the time you deal with these people. So every time you have to give a DML or SELECT statement you have to give a WHERE condition like WHERE DEPTNO=10. To avoid this, you can create a view as given below:
CREATE VIEW accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Now to see the account people you don't have to give a query with where condition β you can just type:
SELECT * from accounts_staff;
The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);
However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);
π Definition β WITH CHECK OPTION: Ensures that any DML operations (INSERT, UPDATE) performed through the view will not create rows that the view itself cannot select.
π Example: The accounts_staff view only shows department 10 employees. Inserting a row with Deptno=30 through this view fails because that row would not be visible in the view.
Data Dictionary Concepts
Data dictionary is a read-only set of tables that provides information about its associated database. A data dictionary contains the definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on). A dictionary, as said above, is built in tables which are created and maintained by the system.
The data dictionary has three primary uses:
- Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.
- Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.
- Any Oracle user can use the data dictionary as a read-only reference for information about the database.
Using Data Dictionary Concepts
Few data dictionary views are mentioned in the table below:
| Data Dictionary View | Description |
|---|---|
SELECT * FROM all_tables; | ALL_TABLES describes all relational tables accessible to the user. |
SELECT * FROM user_constraints; | USER_CONSTRAINTS describes all constraint definitions on tables owned by the current user. |
A complete list of data dictionary views is available online.
Data Dictionary & Views
The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information. Most users are given access to the views rather than the base tables. All data dictionary information is stored in tables, but much of the data is presented to users through views. In other words, users don't get direct access to the tables of the data dictionary; they instead get access to the views, which provide somewhat limited access in order to protect the integrity of the data dictionary. The code of a view can be retrieved by using the data dictionary with the following syntax:
SELECT * FROM user_views;
β Key Takeaways
Views are virtual tables that do not store data themselves but derive it from base tables using pre-defined SELECT statements. The WITH CHECK OPTION is critical for enforcing data integrity when performing DML operations through views β it prevents inserts or updates that would create rows invisible to the view. Data dictionaries are system-maintained read-only tables that store metadata about all database objects; they are automatically updated whenever DDL statements are executed. Users access data dictionary information through views (like ALL_TABLES and USER_CONSTRAINTS) rather than directly querying the underlying system tables, which protects the database's integrity. Understanding views and data dictionaries is essential for database administration, security implementation, and efficient query management.
π§ Quick Revision Questions
- What is a view and how does it differ from a base table in terms of data storage?
- Explain the purpose of the WITH CHECK OPTION clause when creating a view. What happens if you try to insert a row through a view that violates this constraint?
- List the three primary uses of a data dictionary in Oracle.
- Why do users access data dictionary information through views rather than directly querying the system base tables?
- Which data dictionary view would you query to find all constraint definitions on tables owned by the current user?