DBMS FYIT UNIT 2 SQL.docx.pdf

FYBScI.T. SYLLABUS SEMESTER -1 UNIT-1 Database System, Its Applications, Purpose of Database Systems, View of Data, Models, Data Languages, Database Users and Administrator, Database Architecture, ER diagrams, EER Dia Introduction to the Relational Model, Dat...

FYBScI.T. SYLLABUS SEMESTER -1 UNIT-1 Database System, Its Applications, Purpose of Database Systems, View of Data, Models, Data Languages, Database Users and Administrator, Database Architecture, ER diagrams, EER Dia Introduction to the Relational Model, Database Schema, Keys, Relational Algebra – Rela Operations, Tuple Relational Calculus, Domain Relational Calculus, Normalization UNIT-2 SQL: Data definition, Data types, Types of Constraints, SELECT Operation, Where Search cond Aggregate function, Null Values, Set Theory, Joined relations, Subqueries. Views: Introduction to views, Types of Views, Updates on views, comparison between tables and v UNIT-3 Transaction Concept- Transaction State- Implementation of Atomicity and Durability – Concur control – Executions – Serializability- Recoverability – Implementation of Isolation – Testin serializability- Lock –Based Protocols – Timestamp Based Protocols- Validation- Based Protoc Multiple Granularity. Recovery and Atomicity – Log – Based Recovery – Recovery with Conc Transactions – Buffer Management – Failure with loss of nonvolatile storage-Advance Rec systems- Remote Backup systems.  UNIT-2 SQL: Data definition, Data types, Types of Constraints, SELECT Operation, Where Search cond Aggregate function, Null Values, Set Theory, Joined relations, Subqueries. Views: Introduction to views, Types of Views, Updates on views, comparison between tables and view Data types:- Numeric Data Types These contain numbers and are divided into two categories: exact and approximate. Character String Data Types These can contain numbers, alphabets, and symbols, and have types like “char” and “varchar.” Unicode Character String Data Types These are similar to character string data types, but take up twice as much storage space. Binary Data Types The characters in these data types are in the hexadecimal format. Date and Time Data Types  These are used to store date and time information in data types, such as “timestamp” and “year.” Miscellaneous Data Types Remaining SQL data types that serve a variety of functions are grouped together in this category. For example, “CLOB” is used for large character objects and “xml” for XML data. Types of Constraints:- Constraints are the rules that we can apply on the type of data in a table. That is, we can specify the limit on the type of data that can be stored in a particular column in a table using constraints. The available constraints in SQL are: NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more. UNIQUE: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated. PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table. And this constraint is used to specify a field in a table as primary key. FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as Foreign key. CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition. DEFAULT: This constraint specifies a default value for the column when no value is specified by the user. How to specify constraints? We can specify constraints at the time of creating the table using CREATE TABLE statement. We can also specify the constraints after creating a table using ALTER TABLE statement. Syntax: Below is the syntax to create constraints using CREATE TABLE statement at the time of creating the table. CREATE TABLE sample_table ( column1 data_type(size) constraint_name, column2 data_type(size) constraint_name, column3 data_type(size) constraint_name,.... ); sample_table: Name of the table to be created. data_type: Type of data that can be stored in the field. constraint_name: Name of the constraint. for example- NOT NULL, UNIQUE, PRIMARY KEY etc. Let us see each of the constraint in detail. 1. NOT NULL – If we specify a field in a table to be NOT NULL. Then the field will never accept null value. That is, you will be not allowed to insert a new row in the table without specifying any value to this field. For example, the below query creates a table Student with the fields ID and NAME as NOT NULL. That is, we are bound to specify values for these two fields every time we wish to insert a new row. CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, ADDRESS varchar(20) ); 2. UNIQUE – This constraint helps to uniquely identify each row in the table. i.e. for a particular column, all the rows should have unique values. We can have more than one UNIQUE columns in a table. For example, the below query creates a table Student where the field ID is specified as UNIQUE. i.e, no two students can have the same ID. Unique constraint in detail. CREATE TABLE Student ( ID int(6) NOT NULL UNIQUE, NAME varchar(10), ADDRESS varchar(20) ); 3. PRIMARY KEY – Primary Key is a field which uniquely identifies each row in the table. If a field in a table as primary key, then the field will not be able to contain NULL values as well as all the rows should have unique values for this field. So, in other words we can say that this is combination of NOT NULL and UNIQUE constraints. A table can have only one field as primary key. Below query will create a table named Student and specifies the field ID as primary key. CREATE TABLE Student ( ID int(6) NOT NULL UNIQUE, NAME varchar(10), ADDRESS varchar(20), PRIMARY KEY(ID) ); 4. FOREIGN KEY – Foreign Key is a field in a table which uniquely identifies each row of a another table. That is, this field points to primary key of another table. This usually creates a kind of link between the tables. Consider the two tables as shown below: Orders O_ID ORDER_NO C_ID 1 2253 3 2 3325 3 3 4521 2 4 8532 1 Customers C_ID NAME ADDRESS 1 RAMESH DELHI 2 SURESH NOIDA 3 DHARMESH GURGAON As we can see clearly that the field C_ID in Orders table is the primary key in Customers table, i.e. it uniquely identifies each row in the Customers table. Therefore, it is a Foreign Key in Orders table.  Syntax: CREATE TABLE Orders ( O_ID int NOT NULL, ORDER_NO int NOT NULL, C_ID int, PRIMARY KEY (O_ID), FOREIGN KEY (C_ID) REFERENCES Customers(C_ID) ) (i) CHECK – Using the CHECK constraint we can specify a condition for a field, which should be satisfied at the time of entering values for this field. For example, the below query creates a table Student and specifies the condition for the field AGE as (AGE >= 18 ). That is, the user will not be allowed to enter any record in the table with AGE < 18. Check constraint in detail CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, AGE int NOT NULL CHECK (AGE >= 18) ); (ii) DEFAULT – This constraint is used to provide a default value for the fields. That is, if at the time of entering new records in the table if the user does not specify any value for these fields then the default value will be assigned to them. For example, the below query will create a table named Student and specify the default value for the field AGE as 18. CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, AGE int DEFAULT 18 ); 1. SELECT Operation The SELECT statement is used to query data from a database. -- Example: Select all columns from the Employees table SELECT * FROM Employees; -- Example: Select specific columns from the Employees table SELECT EmployeeID, FirstName, LastName FROM Employees; 2. WHERE Search Condition The WHERE clause is used to filter records that meet a specific condition. -- Example: Select employees older than 30 SELECT * FROM Employees WHERE Age > 30; -- Example: Select employees from the Sales department SELECT * FROM Employees WHERE Department = 'Sales'; 3. Aggregate Functions Aggregate functions perform a calculation on a set of values and return a single value. -- Example: Count the total number of employees SELECT COUNT(*) AS TotalEmployees FROM Employees; -- Example: Find the average salary of employees SELECT AVG(Salary) AS AverageSalary FROM Employees; -- Example: Find the maximum salary SELECT MAX(Salary) AS HighestSalary FROM Employees; 4. Null Values Handling NULL values in SQL. -- Example: Select employees with no department assigned SELECT * FROM Employees WHERE Department IS NULL; -- Example: Select employees with a specified department SELECT * FROM Employees WHERE Department IS NOT NULL; 5. Set Theory SQL set operations include UNION, INTERSECT, and EXCEPT. -- Example: Combine results from two tables (removes duplicates) SELECT Name FROM Employees_A UNION SELECT Name FROM Employees_B; -- Example: Find common records between two tables SELECT Name FROM Employees_A INTERSECT SELECT Name FROM Employees_B; -- Example: Find records in Employees_A but not in Employees_B SELECT Name FROM Employees_A EXCEPT SELECT Name FROM Employees_B; 6. Joined Relations Joins combine rows from two or more tables based on a related column. -- Example: Inner Join between Employees and Departments SELECT Employees.Name, Departments.DeptName FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID; -- Example: Left Join between Employees and Departments SELECT Employees.Name, Departments.DeptName FROM Employees LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID; 7. Subqueries Subqueries are queries nested within another query. -- Example: Subquery to find employees in the Sales department SELECT Name FROM Employees WHERE DeptID = (SELECT DeptID FROM Departments WHERE DeptName = 'Sales'); -- Example: Subquery to find employees with salary above average SELECT Name, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees); Diagrams SELECT Operation +---------------------+ | SELECT Operation | +---------------------+ | v +-------------------------------+ | SELECT * FROM Employees; | +-------------------------------+ WHERE Search Condition +------------------------+ | WHERE Search Condition | +------------------------+ | v +-------------------------------+ | SELECT * FROM Employees | | WHERE Age > 30; | +-------------------------------+ Aggregate Functions +----------------------+ | Aggregate Functions | +----------------------+ | v +-------------------------------+ | SELECT COUNT(*) AS TotalEmployees | | FROM Employees; | +-------------------------------+ Null Values +-------------+ | Null Values | +-------------+ | v +-------------------------------+ | SELECT * FROM Employees | | WHERE Department IS NULL; | +-------------------------------+ Set Theory +-----------+ | Set Theory | +-----------+ | v +-------------------------------+ | SELECT Name FROM Employees_A | | UNION | | SELECT Name FROM Employees_B;| +-------------------------------+ Joined Relations +---------------+ | Joined Relations | +---------------+ | v +-------------------------------+ | SELECT Employees.Name, | | Departments.DeptName | | FROM Employees | | INNER JOIN Departments | | ON Employees.DeptID = | | Departments.DeptID; | +-------------------------------+ Subqueries +-----------+ | Subqueries | +-----------+ | v +-------------------------------+ | SELECT Name | | FROM Employees | | WHERE DeptID = ( | | SELECT DeptID | | FROM Departments | | WHERE DeptName = 'Sales'); | +-------------------------------+ These notes cover the basic concepts and usage examples for SQL SELECT operations, search conditions, aggregate functions, handling null values, set theory operations, joins, and subqueries, along with suitable diagrams for visualization. Introduction to Views Views are virtual tables in SQL that present data from one or more underlying tables. They do not store data themselves but provide a way to simplify complex queries, enhance security, and present data in a specific format. Definition: CREATE VIEW view_name AS SELECT column1, column2,... FROM table_name WHERE condition; Types of Views 1. Simple Views: ○ Derived from a single table. ○ Does not contain functions or groups of data. ○ Supports DML operations if based on a single table. Example: CREATE VIEW EmployeeNames AS SELECT FirstName, LastName FROM Employees; Complex Views: ○ Derived from multiple tables. ○ May contain functions, joins, and group data. ○ Usually read-only. Example: CREATE VIEW EmployeeDepartment AS SELECT Employees.FirstName, Employees.LastName, Departments.DeptName FROM Employees JOIN Departments ON Employees.DeptID = Departments.DeptID; Indexed Views (Materialized Views): ○ Physically stores data to improve query performance. ○ Automatically updated as data in the base tables change. Example: CREATE VIEW SalesSummary WITH SCHEMABINDING AS SELECT StoreID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY StoreID; CREATE UNIQUE CLUSTERED INDEX idx_SalesSummary ON SalesSummary(StoreID); Partitioned Views: ○ Union multiple tables into a single logical table. ○ Useful for managing large datasets. Example: CREATE VIEW AllRegionsSales AS SELECT * FROM SalesRegion1 UNION ALL SELECT * FROM SalesRegion2; Updates on Views Simple Views: Can often be updated if they directly reference columns from a single table without any computed columns, group functions, or joins. Complex Views: Generally read-only due to the complexity of the underlying queries. Indexed Views: Can be updated but the underlying base tables must be modified. Partitioned Views: Can be updated if the underlying tables and the view are defined properly to handle such updates. Example: -- Simple view update UPDATE EmployeeNames SET LastName = 'Smith' WHERE FirstName = 'John'; Comparison Between Tables and Views Aspect Tables Views Storage Physically store data Virtual, no physical storage of data Data Source Actual data Derived from tables through queries  Update Can be updated directly Can be updated (depends on the view type) Performance Direct access to data, high May require complex queries performance Security Access controlled at table level Can provide selective access to data Use Cases Store persistent data Simplify complex queries, security Examples and Diagrams Simple View: CREATE VIEW EmployeeNames AS SELECT FirstName, LastName FROM Employees; Diagram: +--------------------+ | EmployeeNames | +--------------------+ | FirstName | LastName | +-----------+----------+ | John | Doe | | Jane | Smith | +--------------------+ Complex View: CREATE VIEW EmployeeDepartment AS SELECT Employees.FirstName, Employees.LastName, Departments.DeptName FROM Employees JOIN Departments ON Employees.DeptID = Departments.DeptID; Diagram: +---------------------------+ | EmployeeDepartment | +---------------------------+ | FirstName | LastName | DeptName | +-----------+----------+-----------+ | John | Doe | HR | | Jane | Smith | IT | +---------------------------+ Indexed View: CREATE VIEW SalesSummary WITH SCHEMABINDING AS SELECT StoreID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY StoreID; CREATE UNIQUE CLUSTERED INDEX idx_SalesSummary ON SalesSummary(StoreID); Diagram: +--------------------+ | SalesSummary | +--------------------+ | StoreID | TotalSales | +---------+------------+ | 1 | 10000 | | 2 | 15000 | +--------------------+ Partitioned View: CREATE VIEW AllRegionsSales AS SELECT * FROM SalesRegion1 UNION ALL SELECT * FROM SalesRegion2; Diagram: +-------------------+ | AllRegionsSales | +-------------------+ | SaleID | Amount | +--------+----------+ | 1 | 500 | | 2 | 700 | | 3 | 600 | | 4 | 800 | +-------------------+