π Lecture 23 β Physical Record and De-normalization Partitioning
π Overview: This lecture focuses on the concepts of denormalization and partitioning in database physical design. These techniques are key to improving database performance by optimizing how data is stored and accessed, balancing efficiency and maintainability.
ποΈ Topics Covered
The lecture begins with an explanation of denormalization as a process of moving from higher to lower normal forms to improve access speed. It discusses common situations for denormalization, its benefits, and drawbacks. The lecture then shifts to database partitioning, detailing its goals, types (horizontal and vertical), and specific methods of horizontal partitioning like range, hash, and list partitioning. Finally, a summary of these concepts is provided along with an exercise.
π Lecture Summary
Physical Record and Denormalization
Denormalization is a technique to convert a database from a highly normalized logical model into a physical model with lower normal forms to speed up data access. Logical design groups fields by primary key, but physical design groups fields as stored and accessed by the DBMS. Denormalization can involve decomposing or combining logical relations to improve system response time, often by reducing costly join operations.
Several indicators suggest when to denormalize: presence of multiple critical queries spanning tables, need to process repeating groups collectively, frequent complex calculations on columns, diverse access patterns by multiple users, or when certain columns are very frequently queried (over 60%). Although new RDBMS releases improve performance, denormalization remains necessary in many scenarios.
π Definition β Denormalization: The process of moving from higher to lower normal forms in database modeling to enhance access speed by physically grouping data differently than logical design.
Denormalization involves a balance between enhanced performance and risks like anomalies or maintenance overhead, guided by detailed view analysis which identifies critical transaction paths and access methods.
Denormalization Situations:
-
Situation 1: Merge two entity types with a one-to-one relationship into a single relation, especially if frequently accessed together, despite some storage wastage.
-
Situation 2: Many-to-many relationships are often implemented with three relations, requiring two join operations for queries. By merging the relation representing the association with one participating entityβs relation (usually the smaller one), the number of joins reduces to one, improving efficiency though violating 2NF and introducing anomalies.
π Example:
Relations:
EMP(empID, eName, pjId, Sal)
PROJ(pjId, pjName)
WORK(empId, pjId, dtHired, Sal)By merging WORK into PROJ:
PROJ(pjId, pjName, empId, dtHired, Sal)
This reduces join operations but causes 2NF anomalies. -
Situation 3: Reference data one-to-many relationships where the 'one' side does not participate in other relationships. The reference table is merged into the many side to eliminate joins and improve performance.
π Example: Merging the HOBBY relation into STUDENT where each hobby can be adopted by many students.
Partitioning
Partitioning is the process of splitting the same relation into parts, contrasting with denormalization which merges relations. Main goals are to reduce workload, balance load, and speed up data processing by, for example, placing frequently accessed data in primary memory.
Types of Partitioning:
-
Horizontal Partitioning: Splits table by rows into smaller tables, improving access time, maintenance, security, and backup. Partitions can be distributed over different disks to reduce contention. Types of horizontal partitioning:
- Range Partitioning: Table rows partitioned based on attribute ranges. For example, student IDs 1-1000 in partition 1, improving efficiency but possibly causing unbalanced partitions.
- Hash Partitioning: Applies a hash function to partition data, reducing unbalanced partitions compared to range partitioning.
- List Partitioning: Specifies exact value lists for each partition rather than ranges.
-
Vertical Partitioning: (Mentioned but not elaborated in this lecture)
π Definition β Partitioning: Dividing a database relation into smaller parts either by rows (horizontal) or columns (vertical) to improve performance and manageability.
π‘ Why this matters: Both denormalization and partitioning are crucial physical design strategies to optimize system response times and balance workload in large and complex databases, especially in client-server environments.
Summary
Denormalization improves processing efficiency but risks increased data maintenance and potential anomalies. It requires thorough testing and analysis of data access paths and end-user views to ensure beneficial results. Partitioning requires careful analysis of relation usage before implementation. Both techniques aim to optimize system performance by reducing expensive operations and improving data access paths.
β Key Takeaways
- Denormalization strategically lowers normal forms to reduce join operations and speed up database queries but must balance performance gains against data anomalies and maintenance costs.
- Situations ideal for denormalization include one-to-one merges, reducing many-to-many join operations, and merging reference data for one-to-many relationships.
- Partitioning splits large relations into smaller parts (usually horizontally by rows) to improve access time, workload balance, and scalability.
- Types of horizontal partitioning include range, hash, and list partitioning, each with specific advantages and potential challenges like partition balance.
- Both denormalization and partitioning need detailed analysis of data access patterns and robust testing to ensure they positively impact database performance.
π§ Quick Revision Questions
- What is denormalization and why is it used in physical database design?
- Describe three situations where denormalization is beneficial.
- How does merging the WORK relation with PROJ reduce join operations in many-to-many relationships?
- What are the main goals of database partitioning?
- Explain the differences between range, hash, and list partitioning in horizontal partitioning.
π Lecture 24 β Vertical Partitioning, Replication and Structured Query Language (SQL)
π Overview: This lecture covers advanced physical database design techniques, focusing on vertical partitioning, replication, and an introduction to Structured Query Language (SQL). Understanding these concepts is crucial for optimizing database performance and managing distributed data effectively.
ποΈ Topics Covered
The lecture begins with vertical partitioning, explaining how tables can be split by columns to improve access patterns while maintaining the primary key for reconstruction. Next, it explores replication, the process of copying and synchronizing database portions to increase speed and fault tolerance. The lecture also discusses clustering files for related data proximity. Finally, an introduction to SQL is provided, covering its standardization, benefits, and use in databases with specific reference to Microsoft SQL Server tools.
π Lecture Summary
Vertical Partitioning
Vertical partitioning splits a table based on its attributes rather than rows. The purpose is to optimize access patterns by dividing a relation into subsets of columns, replicating the primary key across partitions for reconstruction of the original table. Unlike horizontal partitioning, vertical partitioning allows sending only required columns to other machines.
Example given: The student relation STD splits into STD and STDACD tables, partitioning attributes based on their access needs.
π‘ Why this matters: Vertical partitioning can reduce data transmission and speed up queries by limiting column subsets.
π Definition β Vertical Partitioning: The process of splitting a table into different physical records based on its attributes, with the primary key repeated in all partitions.
π Example:
Original table:
STD(stId, sName, sAdr, sPhone, cgpa, prName, school, mtMrks, mtSubs, clgName, intMarks, intSubs, dClg, bMarks, bSubs)
Vertical partitions:
- STD(stId, sName, sAdr, sPhone, cgpa, prName)
- STDACD(sId, school, mtMrks, mtSubs, clgName, intMarks, intSubs, dClg, bMarks, bSubs)
Replication
Replication involves copying parts of a database from one environment to another, keeping copies synchronized with the original source. Changes to the source propagate to the copies, enhancing access speed and minimizing damage in case of failure. Replication can copy entire tables or parts of tables. Frequent updates may decrease performance due to synchronization overhead, so replication suits mostly read-heavy applications.
π Definition β Replication: The process of copying data portions and maintaining synchronization between the original database and its copies.
π‘ Why this matters: Replication increases availability and fault tolerance but requires careful management when updates are frequent.
Clustering Files
Clustering places records from different tables physically adjacent on storage, improving efficiency by minimizing disk seek times when related records are accessed. Clusters are defined by associating keys and grouping tables accordingly. Clustering is best for relatively static situations.
π Definition β Clustering: The process of grouping related records from different tables into adjacent physical locations to improve access efficiency.
Summary of Physical Database Design
Designing a physical database involves transforming a logical data model into an actual implementation optimized for the chosen DBMS. This transformation requires:
- Understanding DBMS features such as indexing, referential integrity, data types, constraints, and configuration parameters.
- Using Data Definition Language (DDL) for realization of physical objects.
- Decisions on nullability, data length types, sequence/identity columns, referential constraints, index types (b-tree, bitmap, hash, etc.), clustering sequences, and file organization must be made.
π‘ Why this matters: Adequate knowledge of DBMS capabilities and configuration is vital to create efficient physical designs that optimize storage and query performance.
Structured Query Language (SQL)
SQL is the ANSI standard language for accessing and manipulating relational databases, currently standardized as SQL-92. All compliant DBMSs support SQL, though many add proprietary extensions. SQL functionalities include data retrieval, updates, table creation, deletion, and more. Popular DBMSs using SQL include Oracle, Sybase, Microsoft SQL Server, Access, and Ingres.
Benefits of SQL:
- Reduced training costs
- Application portability and longevity
- Reduced vendor dependency
- Cross-system communication
The lecture introduces Microsoft SQL Server 2000 desktop edition as the course DBMS, emphasizing Query Analyzer for SQL practice over Enterprise Manager.
π Definition β SQL: A standardized computer language for accessing and manipulating relational databases.
β Key Takeaways
- Vertical partitioning divides tables by columns, repeating primary keys to facilitate reconstruction, improving data access efficiency.
- Replication copies and synchronizes data across environments, enhancing speed and fault tolerance but adding overhead during updates.
- Clustering organizes related records physically close to optimize access speed, suitable for static datasets.
- Transforming a logical data model to a physical database requires deep knowledge of DBMS features and careful design decisions about indexes, constraints, and storage.
- SQL is the universal language for relational databases; mastering it enables manipulation of data and database objects across platforms, with practical applications in web development and enterprise systems.
π§ Quick Revision Questions
- What distinguishes vertical partitioning from horizontal partitioning in database design?
- How does replication improve database performance, and what challenges does it present?
- What is the main purpose of clustering files in a database?
- What are the critical considerations when transforming a logical data model into a physical database design?
- Why is SQL standardized, and what are the major benefits of using SQL in database management?
π Lecture 25 β Overview of Structured Query Language (SQL)
π Overview: This lecture introduces Structured Query Language (SQL), the standard language for interacting with relational databases. It covers SQL syntax rules, data types in SQL Server, and the classification of SQL commands, providing foundational knowledge for database manipulation and management.
ποΈ Topics Covered
The lecture begins with the rules of SQL syntax, including reserved words, identifiers, and command formatting. It continues with an overview of SQL Server data types such as integers, text, money, floating point, and date types. The lecture then illustrates a practical example using an examination system database schema and explains the definitions and usage of Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). Finally, it summarizes the importance of SQL and its usage in various relational database systems.
π Lecture Summary
Rules of SQL Format
SQL is a basic language to query and manipulate data in a database. Commands are written with reserved words in uppercase (e.g., SELECT, INSERT) and user-defined identifiers in lowercase. Valid identifiers can start with @, _, letters, or numbers, with a maximum length of 256 characters. Reserved words cannot be used as identifiers. Optional components in commands are enclosed in square brackets [ ], required items in curly braces { }, "|" indicates choices, and commas separate multiple items.
π Definition β Reserved Words: Words predefined in SQL language that must be written in uppercase and cannot be used as identifiers.
π Example:
SELECT [ALL|DISTINCT] { * | select_list } FROM {table | view[, ...n]} where ALL, DISTINCT, SELECT, FROM are reserved words, and table/view names are identifiers.
Data Types in SQL Server
SQL Server assigns a data type to every column, variable, expression, and parameter, which defines the kind of data it can hold. The system supplies various data types:
- Integers: bigint (up to 64-bit values), int (32-bit), smallint (16-bit), tinyint (0 to 255), bit (1 or 0).
- Decimal and Numeric: fixed precision/scale numeric data.
- Text: char (fixed length, default 30, max 8000), varchar (variable length), text (auto variable length), nchar, nvarchar, ntext.
- Money: smallmoney (6 digits, 4 decimal), money (15 digits, 4 decimal).
- Floating point: float and real.
- Date: smalldatetime and datetime.
π‘ Why this matters: Correct use of data types ensures data integrity, efficient storage, and accurate query results.
Examination System Database Example
The lecture presents a conceptual database schema for an examination system, which is then translated into a relational design with tables like:
PROGRAM(prName, totSem, prCredits)COURSE(crCode, crName, crCredits, prName)SEMESTER(semName, stDate, endDate)CROFRD(crCode, semName, facId)FACULTY(facId, fName, fQual, fSal, rank)STUDENT(stId, stName, stFName, stAdres, stPhone, prName, curSem, cgpa)ENROLL(stId, crCode, semName, mTerm, sMrks, fMrks, totMrks, grade, gp)SEM_RES(stId, semName, totCrs, totCrdts, totGP, gpa)
This exemplifies applying SQL to organize data for academic records.
Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL)
DDL commands define the database schema and structure, such as creating and deleting tables. After compiling DDL statements, system metadata is stored in a data dictionary describing storage and access methods.
DML allows users to retrieve, insert, update, and delete data. There are two types:
- Procedural: specifying what data and how to fetch it.
- Nonprocedural: specifying only what data is needed.
DCL controls database access rights through commands like GRANT and REVOKE.
π Definition β DDL: Language commands to define or modify database structure. π Definition β DML: Language commands to manipulate data within database objects. π Definition β DCL: Commands that control access and permissions on the database.
Summary
SQL is the ANSI standard language for communication with relational databases. Systems like Oracle, Sybase, Microsoft SQL Server, Access, and Ingres use SQL, often with proprietary extensions. The basic SQL commands (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP) perform most database operations. An understanding of SQL command categories and data types is essential for efficient database use.
β Key Takeaways
- SQL commands have strict formatting rules involving reserved words and identifiers.
- SQL Server supports diverse data types for integers, text, money, floating point, and dates, crucial for accurate data representation.
- Relational database designs use tables with attributes matching data types for real-world entities, exemplified by the examination system database.
- SQL commands are divided into DDL (structure), DML (data manipulation), and DCL (access control), each serving distinct roles.
- Mastery of SQL basics enables effective querying, updating, and managing data across various relational DBMS platforms.
π§ Quick Revision Questions
- What are the rules regarding the use of reserved words and identifiers in SQL?
- Name three integer data types supported by SQL Server and their value ranges.
- What is the difference between Procedural and Nonprocedural DML?
- What purpose does the Data Dictionary serve in SQL databases?
- List the three main categories of SQL commands and give one example for each.
π Lecture 26 β Different Commands of SQL
π Overview: This lecture covers the different commands of SQL, focusing primarily on the Data Definition Language (DDL). Understanding SQL commands is essential for managing databases effectively, particularly for creating and structuring database schemas.
ποΈ Topics Covered
The lecture begins with a brief recap of SQL command categories: DDL, DML, and DCL. It then dives deeply into DDL, explaining its role in defining database structures, schemas, constraints, and storage information. The core focus is on the CREATE command, demonstrating how to create databases, tables, and apply various constraints through practical examples.
π Lecture Summary
Categories of SQL Commands
SQL commands are divided into three categories: DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language). This lecture focuses on DDL, which is used to define the structure of a database including schemas, domains of attributes, constraints, indexes, security, and physical storage.
π Definition β DDL (Data Definition Language): Allows specification of database structure such as schemas, attribute domains, integrity constraints, indexes, security, and physical storage.
DDL and CREATE Command
DDL commands affect the database structure. The most important DDL command introduced here is CREATE, which is used to create databases, tables, fields, views, and indexes. The CREATE command starts with creating a database followed by creating tables within it.
The syntax to create a database is:
π Formula: CREATE DATABASE db_name β Creates a new database with the specified name.
Example:
π Example: CREATE DATABASE EXAM; creates a database named EXAM.
Two main approaches to create tables are by SQL commands or using a graphical interface like Enterprise Manager. The CREATE TABLE command defines table attributes, including data types and constraints such as primary keys, foreign keys, not nulls, and checks.
The general syntax to create tables is: π Formula:
CREATE TABLE
[database_name.[owner]. | owner.] table_name
(
{ <column_definition>
| column_name AS computed_column_expression
| <table_constraint>
}
| [ { PRIMARY KEY | UNIQUE } [ ,...n ] ]
)
π‘ Why this matters: Specifying constraints at table creation enforces data integrity and prevents invalid data entry.
Table Creation Details
A valid table name must begin with a letter and can include alphanumeric characters and underscores. Each column requires a name and a valid data type, such as char, varchar, tinyint, smallint, real, or text.
Column definitions can include:
- DEFAULT values
- NULL or NOT NULL constraints
- PRIMARY KEY or UNIQUE constraints
- FOREIGN KEY references with options for ON DELETE/ON UPDATE CASCADE or NO ACTION
- CHECK constraints with logical expressions
π Definition β Column_constraint: Rules applied to individual columns such as NOT NULL, PRIMARY KEY, FOREIGN KEY, and CHECK conditions.
CREATE TABLE Examples
- Simple table with three attributes:
CREATE TABLE Program (
prName char(4),
totSem tinyint,
prCredits smallint
)
- Table with multiple data types:
CREATE TABLE Student (
stId char(5),
stName char(25),
stFName char(25),
stAdres text,
stPhone char(10),
prName char(4),
curSem smallint,
cgpa real
)
- Table creation with constraints:
CREATE TABLE Student (
stId char(5) constraint ST_PK primary key constraint ST_CK check (stId like 'S[0-9][0-9][0-9][0-9]'),
stName char(25) not null,
stFName char(25),
stAdres text,
stPhone char(10),
prName char(4),
curSem smallint default 1,
cgpa real
)
Each constraint is named meaningfully to allow references later. For example, the CHECK constraint verifies the student ID format, ensuring it follows a particular pattern.
Summary
Proper database design and understanding the CREATE command are critical for database success. This lecture emphasized how to create databases and tables using SQL with various constraints to enforce integrity. Practicing the CREATE command is essential as it lays the foundation for all database operations.
β Key Takeaways
- SQL commands are categorized into DDL, DML, and DCL; DDL deals with database structure.
- The CREATE command is fundamental for creating databases, tables, and applying constraints.
- Tables require valid names, correct data types, and constraints like NOT NULL, PRIMARY KEY, and CHECK.
- Naming constraints explicitly is important for future reference and maintenance.
- Understanding and practicing CREATE with constraints ensure data integrity and effective database design.
π§ Quick Revision Questions
- What are the three categories of SQL commands and which one deals with database structure?
- Write the SQL command to create a database named "School".
- What are some types of constraints you can apply when creating a table?
- Why should constraints in SQL be given meaningful names?
- Explain the difference between a temporary table and a permanent table in SQL creation.
π Lecture 27 β Data Manipulation Language
π Overview: This lecture focuses on the ALTER SQL statement and other SQL commands that modify table structures or data. It introduces the Data Manipulation Language (DML), which allows retrieval, insertion, deletion, and modification of database information, and explains key SQL commands like INSERT, TRUNCATE, DELETE, and DROP.
ποΈ Topics Covered
The lecture begins with a detailed explanation of the ALTER TABLE statement, showing how to add, modify, drop, or rename columns in existing tables. It then covers commands to remove rows (TRUNCATE and DELETE) and drop entire tables (DROP). The lecture concludes by introducing Data Manipulation Language (DML), focusing on its role as a non-procedural language, and the core DML commands: INSERT, SELECT, UPDATE, and DELETE.
π Lecture Summary
Alter Table Statement
The ALTER TABLE command changes a tableβs structure after it has been created. It allows adding new columns, altering existing columnsβ data types or default values, dropping columns, and renaming columns. The syntax includes specifying the table name and the action (ADD, ALTER, DROP, RENAME) on columns. Constraints can also be added or removed using this command, as demonstrated by adding a foreign key and dropping constraints.
π Definition β ALTER TABLE: A SQL statement used to modify the definition of an existing table by adding, altering, dropping, or renaming columns and constraints.
π Syntax:
ALTER TABLE table {
ADD [COLUMN] column type [(size)] [DEFAULT default] |
ALTER [COLUMN] column type [(size)] [DEFAULT default] |
ALTER [COLUMN] column SET DEFAULT default |
DROP [COLUMN] column |
RENAME [COLUMN] column TO columnNew
}
π Example:
ALTER TABLE Student
ADD CONSTRAINT fk_st_pr
FOREIGN KEY (prName) REFERENCES Program (prName)
This example adds a foreign key constraint to the Student table referencing the Program table.
Other examples show modifying a columnβs datatype (ALTER COLUMN stFName char(20)), dropping a column (DROP COLUMN curSem), and dropping a constraint (DROP CONSTRAINT ck_st_pr).
Removing Rows or Tables
- TRUNCATE TABLE removes all rows but keeps the table structure intact.
- DELETE removes one or more specific rows based on a condition; without a condition, it removes all rows.
- DROP TABLE completely deletes the table and its data from the database.
π Definitions:
- TRUNCATE TABLE: Deletes all rows from a table but retains its structure.
- DELETE: Removes rows based on conditions or all rows if no condition is specified.
- DROP TABLE: Deletes the entire table and its data permanently.
π Examples:
TRUNCATE TABLE class
DROP TABLE table_name
Data Manipulation Language
SQL is a non-procedural language that emphasizes specifying what data is needed rather than how to obtain it, distinguishing it from procedural languages like C or COBOL. DML is used to retrieve, insert, modify, and delete data in databases.
There are two types of DML approaches:
- Procedural: User specifies what data to retrieve and how to get it.
- Non-procedural: User specifies only what data is needed.
Basic DML statements include:
- INSERT: adds new rows to tables.
- SELECT: retrieves rows from tables.
- UPDATE: modifies existing rows.
- DELETE: removes rows.
Insert Command
The INSERT command adds new records to a table. Users must ensure:
- Data types for inserted values match the column data types.
- Data size should be within the column's size limits.
- Values correspond to the correct column order in the table.
Syntax of INSERT command:
INSERT [INTO] table [(column_list)]
VALUES ({DEFAULT | NULL | expression} [, ...])
π Definition β INSERT: A SQL statement to add records to an existing table.
π The three rules of INSERT:
- Values must match column data types.
- Values must fit within column sizes.
- Values must follow the column order.
The lecture notes that INSERT has two variations:
- INSERT...VALUES for individual records.
- INSERT...SELECT to insert multiple records from a SELECT result.
β Key Takeaways
- ALTER TABLE is a powerful command to modify table structure, including adding/dropping columns and constraints.
- TRUNCATE deletes all rows efficiently but keeps the table intact, whereas DELETE can remove specific rows or all rows.
- DROP TABLE removes the entire table permanently from the database.
- SQLβs Data Manipulation Language (DML) focuses on non-procedural data access, enabling easier, high-level operations on database data.
- Proper data types and sizes must be maintained during INSERT operations to avoid errors.
π§ Quick Revision Questions
- What is the purpose of the ALTER TABLE statement?
- How does TRUNCATE TABLE differ from DELETE in SQL?
- What are the three essential rules when using the INSERT command?
- Define Data Manipulation Language and explain why SQL is called a non-procedural language.
- What SQL statement would you use to completely remove a table and all its data from the database?
π Lecture 28 β Data Manipulation Language: Insert and Select Statements
π Overview: This lecture focuses on the Data Manipulation Language (DML) commands in SQL, specifically the INSERT and SELECT statements. Understanding these commands is essential for adding and retrieving data efficiently in database management.
ποΈ Topics Covered
The lecture begins with an explanation of the INSERT statement, its two forms, and examples illustrating both single and multiple column inserts. Next, it introduces the SELECT statement, detailing its clauses (SELECT, FROM, WHERE), usage of the * wildcard, attribute aliasing, expressions, and the DISTINCT keyword. Several practical examples demonstrate querying data and filtering results.
π Lecture Summary
Insert Statement
The INSERT statement allows insertion of single or multiple records into an existing table with two formats:
INSERT INTO table-1 [(column-list)] VALUES (value-list)INSERT INTO table-1 [(column-list)] (query-specification)
The first inserts explicit values, while the second inserts rows returned by a query. Unlisted columns not specified in the column-list are set to NULL, so those must allow nulls. The VALUES clause contains literal values or scalar expressions matching the data types of the columns.
Example with the COURSE table:
INSERT INTO course VALUES (βCS-211', βOperating Systemsβ, 4, βMCSβ)
This inserts values for all attributes without specifying column names.
Example inserting partial columns:
INSERT INTO course (crCode, crName) VALUES (βCS-316β, βDatabase Systemsβ)
Here, the other attributes must allow NULL since they are omitted.
Example inserting with NULL values for some attributes without specifying columns:
INSERT INTO course VALUES (βMG-103β, βIntro to Managementβ, NULL, NULL)
π Definition β INSERT statement: SQL command to add one or more new rows to an existing table in two formats: explicit value list or query-based insertion.
π Example: See examples above with table COURSE.
Select Statement
The SELECT statement queries data from a database and consists of three main clauses:
- SELECT specifies columns to retrieve (mandatory)
- FROM specifies tables accessed (mandatory)
- WHERE specifies row-filtering conditions (optional)
Basic syntax:
SELECT {*|col_name[,....n]} FROM table_name
Using * returns all columns. Selecting specific columns requires naming them.
Example with STUDENT table:
SELECT * FROM student
returns all rows and columns.
To select specific columns:
SELECT stName, prName FROM student
returns only student names and program names.
Attribute Alias
You can rename columns in the result using AS or directly following the column name:
SELECT stName AS βStudent Nameβ, prName βProgramβ FROM student
Expressions in SELECT
You can include arithmetic expressions computed per row in the SELECT list:
SELECT stId, crCode, mTerm + sMrks βTotal out of 50β FROM enroll
DISTINCT Keyword
Use DISTINCT to return only unique values in a column:
SELECT DISTINCT prName FROM student
returns unique program names without duplicates.
WHERE Clause
The WHERE clause filters rows based on logical expressions (predicates) evaluating to TRUE, FALSE, or UNKNOWN (nulls). Comparisons can be between columns and literals or between two columns.
Example predicate:
Color = 'Red'
is TRUE if color is Red, FALSE otherwise, UNKNOWN if null.
π‘ Why this matters: Filtering with WHERE is crucial for retrieving relevant data subsets efficiently.
β Key Takeaways
- The INSERT statement can add data by specifying explicit values or via results of a query.
- Unlisted columns in an INSERT default to NULL; they must allow null values.
- The SELECT statement retrieves data and has mandatory SELECT and FROM clauses, with WHERE optional for filtering.
- Using
*retrieves all columns; specific columns can be listed explicitly. - DISTINCT removes duplicates from the output.
- WHERE clause predicates filter rows based on logical conditions.
- Alias columns for clearer output and use expressions to compute results dynamically.
π§ Quick Revision Questions
- What are the two forms of the INSERT statement in SQL?
- How does the VALUES clause in an INSERT relate to the columns in the table?
- What are the mandatory clauses in a SELECT statement?
- How does the DISTINCT keyword affect the results of a SELECT query?
- Explain the role of the WHERE clause in a SELECT statement.
π Lecture 29 β Data Manipulation Language
π Overview: This lecture focuses on the WHERE clause in SQL, a fundamental tool for filtering query results in database operations. Understanding how to use the WHERE clause along with various conditions and operators is essential for precise data manipulation and retrieval.
ποΈ Topics Covered
The lecture begins with the syntax and purpose of the WHERE clause in SQL statements, explaining how to specify search conditions. It covers various predicates and operators like BETWEEN, IN, and LIKE, as well as the combination of conditions with logical operators like AND, OR, and NOT. Examples illustrate the use of WHERE in filtering rows and joining tables, concluding with a brief introduction to the ORDER BY clause.
π Lecture Summary
Overview of WHERE Clause
The WHERE clause is used in SQL to filter rows returned by queries such as SELECT, INSERT, UPDATE, or DELETE. The clause selects only the rows that satisfy the specified search condition. The general format is:
SELECT [ALL|DISTINCT] {*|column_list [alias][,...]} FROM table_name [WHERE <search_condition>]
The search condition can involve one or more predicates combined with logical operators. When the condition is met, the corresponding rows appear in the query result.
π Definition β WHERE clause: Used to specify conditions that filter which rows are returned by SQL statements.
π Definition β Search Condition: An expression involving predicates and logical operators that determines which rows satisfy the query's condition.
Search Condition and Predicates
A search condition may include predicates using comparison operators such as =, <>, !=, >, >=, <, <=, combined with NOT, AND, OR, and parentheses to define complex conditions. Predicates can involve expressions, string matching with LIKE, range selection with BETWEEN, null value checks with IS NULL, membership tests with IN, and subqueries with EXISTS.
Example:
SELECT * FROM supplier WHERE supplier_name = 'IBM';
This returns all rows from the supplier table where supplier_name is "IBM".
π Definition β Predicate: A condition in a WHERE clause that returns true or false, involving expressions and operators.
Multiple Conditions and Joins
The WHERE clause can include multiple conditions combined with AND and OR to filter data more precisely. For example:
SELECT supplier_id FROM supplier WHERE supplier_name = 'IBM' OR supplier_city = 'Karachi';
Returns rows where either condition is true.
WHERE can also be used to join tables by specifying matching rows across tables:
SELECT supplier.supplier_name, orders.order_id
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id AND supplier.supplier_city = 'Karachi';
This returns supplier names and order IDs where supplier city is Karachi and supplier IDs match.
Filtering with NOT
To select rows excluding a condition, NOT can be applied:
SELECT crCode, crName, prName FROM course WHERE NOT (prName = 'MCS');
This returns courses not belonging to the MCS program.
BETWEEN Operator
The BETWEEN operator selects rows with values in an inclusive range. Syntax:
SELECT columns FROM tables WHERE column BETWEEN value1 AND value2;
Example:
SELECT * FROM suppliers WHERE supplier_id BETWEEN 10 AND 50;
Returns suppliers with IDs from 10 to 50 inclusive.
The NOT BETWEEN operator selects rows outside the specified range.
π Definition β BETWEEN: Used to filter records with column values within a given inclusive range.
π‘ Why this matters: It simplifies checks for ranges, improving query clarity and efficiency.
IN Operator
The IN operator checks if a columnβs value matches any value in a list, which simplifies multiple OR conditions. Syntax:
SELECT columns FROM tables WHERE column IN (value1, value2, ..., value_n);
Example:
SELECT crName, prName FROM course WHERE prName IN ('MCS', 'BCS');
Equivalent to:
SELECT crName, prName FROM course WHERE (prName = 'MCS') OR (prName = 'BCS');
This reduces redundancy in queries.
LIKE Operator and Wildcards
The LIKE operator provides pattern matching in string searches with wildcards:
%matches any string of any length (including zero length)_matches exactly one character
Example:
SELECT crName, crCrdts, prName FROM course WHERE prName LIKE '%CS';
Returns courses where the program name ends with "CS".
π Definition β LIKE: Allows pattern matching using wildcards in string comparisons.
π‘ Why this matters: Enables flexible searches such as partial matches and wildcards in queries.
ORDER BY Clause (Introduction)
The ORDER BY clause sorts the records in the result set by specified columns, either ascending (ASC) or descending (DESC) order. This clause is used only with SELECT statements.
Syntax:
SELECT columns FROM tables WHERE predicates ORDER BY column ASC/DESC;
If ASC or DESC is omitted, ascending order (ASC) is the default. Example usage will be covered in the next lecture.
β Key Takeaways
- The WHERE clause is crucial for filtering rows in SQL queries using various search conditions and predicates.
- Multiple predicates can be combined with AND, OR, and NOT for complex filtering and for joining related tables.
- BETWEEN and IN operators simplify comparisons for ranges and multiple values, respectively.
- The LIKE operator with wildcards
%and_allows flexible pattern matching in strings. - The ORDER BY clause sorts query results but was only introduced briefly and will be covered in more detail later.
π§ Quick Revision Questions
- What is the primary purpose of the WHERE clause in SQL?
- How do AND, OR, and NOT affect conditions in a WHERE clause?
- Explain the difference between BETWEEN and IN operators with examples.
- What wildcards are used with the LIKE operator, and how do they function?
- Can the ORDER BY clause be used with INSERT or UPDATE statements? Why or why not?
π Lecture 30 β Data Manipulation Language Functions in SQL
π Overview: This lecture focuses on key SQL features for data manipulation, emphasizing the use of the ORDER BY clause for sorting results, various SQL functions including built-in and aggregate functions, and techniques to access data from multiple tables. Understanding these concepts is crucial for effective query writing and data analysis in relational databases.
ποΈ Topics Covered
The lecture begins with the ORDER BY clause, explaining how it sorts query results. It then introduces functions in SQL, categorizing them into mathematical, string, date, system, and conversion functions, accompanied by examples. The session advances to aggregate functions and the GROUP BY clause to summarize data, followed by the HAVING clause to filter grouped results. Lastly, it covers accessing data from multiple tables, introducing the Cartesian product as a starting point.
π Lecture Summary
ORDER BY Clause
The ORDER BY clause sorts the records in SELECT query results by specified columns in ascending (ASC) or descending (DESC) order. When ASC or DESC is not indicated, ascending order is the default. It is only used with SELECT statements.
Example:
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;
This returns supplier cities of IBM sorted ascendingly. Using DESC sorts it descendingly.
π Definition β ORDER BY Clause: Clause to sort the result set based on specified columns in SELECT statements.
π Example:
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
Sorts supplier cities for IBM in descending order.
Functions in SQL
A function in SQL is a single-word command returning a single value, optionally based on input parameters (e.g., averaging a list). Functions can be built-in (provided by SQL) or user-defined. SQL Server categorizes functions as Mathematical (ABS, ROUND), String (LOWER, UPPER), Date (DATEDIFF), System (USER), and Conversion (CAST).
Example of nested functions:
SELECT upper(stName), lower(stFName), stAdres, len(convert(char, stAdres))
FROM student;
This query converts student names to upper and lower case and calculates the length of the address by first converting it to a character string.
π Definition β Function: A command returning a single value, optionally using input parameters.
π Example: Using UPPER and LOWER string functions and LEN with CONVERT.
Aggregate Functions
These functions operate on multiple rows but return a single value. Common aggregates include AVG, COUNT, MIN, MAX, and SUM. When used with other columns, the GROUP BY clause is mandatory to group the data accordingly; otherwise, no group by is needed if only the aggregate is retrieved.
Example:
SELECT avg(cgpa) as 'Average CGPA', max(cgpa) as 'Maximum CGPA'
FROM student;
π Definition β Aggregate Function: Functions operating on sets of rows returning a single summarized value.
π Example: Calculating average and maximum CGPA.
GROUP BY Clause
The GROUP BY clause groups results by one or more columns for aggregate calculations within those groups. Its syntax requires grouping all non-aggregate columns to produce grouped summaries.
Syntax:
SELECT column1, column2, ..., aggregate_function(expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;
Example:
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;
This returns total sales per department.
π Definition β GROUP BY Clause: Groups rows sharing column values to apply aggregate functions per group.
π Example: SUM of sales grouped by department.
HAVING Clause
The HAVING clause filters records after grouping, applying conditions on aggregate function results. Unlike WHERE, HAVING is designed to filter grouped data based on aggregate computations.
Syntax:
SELECT column1, ..., aggregate_function(expression)
FROM tables
WHERE predicates
GROUP BY column1, ...
HAVING condition;
Examples:
- Filter departments with sales above 1000:
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
- Filter departments with more than 10 employees earning over Rs 25,000:
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
π Definition β HAVING Clause: Filters grouped query results based on aggregate function conditions.
π Example: Using HAVING to display departments with total sales > 1000.
Accessing Multiple Tables
Accessing data from multiple tables is essential and depends on referential integrity constraints. One basic method is the Cartesian product, which returns all possible combinations of rows from two or more tables resulting in m x n rows. It requires no special JOIN keyword, just listing tables in FROM clause. Column names that appear in multiple tables must be qualified.
Example:
SELECT * FROM program, course;
This produces the Cartesian product of all rows in program and course tables. Cartesian product can also be applied to more than two tables or even the same table twice.
π Definition β Cartesian Product: Combination of each row of one table with every row of another table resulting in all possible pairs.
π Example: Selecting all data from the product of Student, Class, and Program tables.
β Key Takeaways
- The ORDER BY clause sorts result sets in ascending or descending order and is only used with SELECT statements.
- SQL supports various function types: mathematical, string, date, system, and conversion, useful for manipulating and retrieving formatted data.
- Aggregate functions summarize data across multiple rows, requiring the GROUP BY clause to group data according to column(s).
- The HAVING clause filters grouped results based on aggregate conditions, unlike WHERE which filters rows before grouping.
- Accessing data from multiple tables starts with the Cartesian product, producing all combinations of rows; more efficient joins are covered later.
π§ Quick Revision Questions
- What does the ORDER BY clause do and when can it be used?
- Name at least three categories of built-in SQL functions and give one example of each.
- How does the GROUP BY clause work with aggregate functions? Provide an example.
- What is the purpose of the HAVING clause and how does it differ from WHERE?
- Explain what a Cartesian product is in the context of accessing multiple tables with an example.