Unit 3

Lesson 7: Fundamentals of Database Systems

Learning Outcomes

• Explain ACID properties of databases
• Understand SQL as a standard language for RDBMS
• Understand the basic structure of SQL
• Write SQL queries to perform basic operations
• Write SQL queries to perform set operations

SQL Queries

Selecting Entries from a Table

a. Select all entries

SELECT * FROM Users;

b. Select specific columns

SELECT Username, F_Name, L_Name FROM Users WHERE Gender = 'Male';

c. WHERE Clause Predicate

SELECT * FROM Users WHERE Gender = 'Male';

d. WHERE Clause with AND Operator

SELECT * FROM Users WHERE Gender = 'Male' AND DOB > '1990-01-01';

e. WHERE Clause with OR Operator

SELECT * FROM Users WHERE Gender = 'Male' OR Gender = 'Female';

f. ORDER BY Clause

SELECT * FROM Users WHERE Gender = 'Male' ORDER BY L_Name;
SELECT * FROM Users WHERE Gender = 'Male' ORDER BY L_Name DESC;

Selecting Entries from Multiple Tables

SELECT * FROM Ticket, Users WHERE Ticket.Booked_by_user = Users.Username AND Users.Gender = 'Male';
SELECT Ticket.PNR_No, Ticket.Flight_no, Ticket.Passenger_name FROM Ticket, Users WHERE Ticket.Booked_by_user = Users.Username AND Users.Gender = 'Male';

Altering a Table

Add a Column

ALTER TABLE Users ADD COLUMN Email VARCHAR(255);

Modify a Column

ALTER TABLE Users ALTER COLUMN DOB TYPE TIMESTAMP;

Renaming Table and Columns

ALTER TABLE Users RENAME TO Customers;
ALTER TABLE Customers RENAME COLUMN F_Name TO First_Name;

LIKE Operator Examples

SELECT Username FROM Users WHERE Username LIKE 'user%';
SELECT Username FROM Users WHERE Username LIKE '%3';
SELECT Username FROM Users WHERE Username LIKE '__ser%';

Note: % matches any substring, _ matches any character.

Using AS (Alias)

SELECT Username AS User_ID FROM Users;
SELECT u.Username, t.Flight_no FROM Users AS u JOIN Ticket AS t ON u.Username = t.Booked_by_user;

Deleting Entries from a Table

a. Delete all entries

DELETE FROM Users;

b. Delete specific entries

DELETE FROM Users WHERE Username='user1';

Dropping a Table

DROP TABLE User_Cancellation;
DROP TABLE Users;

Deleting a Database

DROP DATABASE airlineticketingdb;

References

• ACID Properties in DBMS - Javatpoint
• ACID Properties in DBMS - GeeksforGeeks
• ER Diagram for Airline Reservation System - YouTube

Lesson 7

Additional SQL Commands

WHERE Clause with ORDER BY

SELECT * FROM Users WHERE Gender = 'Male' ORDER BY L_Name;
SELECT * FROM Users WHERE Gender = 'Male' ORDER BY L_Name DESC;

Selecting Data from Multiple Tables

SELECT * FROM Ticket, Users WHERE Ticket.Booked_by_user = Users.Username AND Users.Gender = 'Male';
SELECT Ticket.PNR_No, Ticket.Flight_no, Ticket.Passenger_name FROM Ticket, Users WHERE Ticket.Booked_by_user = Users.Username AND Users.Gender = 'Male';

Additional Basic Operations

LIKE Operator

SELECT Username FROM Users WHERE Username LIKE 'user%';
SELECT Username FROM Users WHERE Username LIKE '%3';
SELECT Username FROM Users WHERE Username LIKE '__ser%';

Using AS for Aliasing

SELECT Username AS User_ID FROM Users;
SELECT u.Username, t.Flight_no FROM Users AS u JOIN Ticket AS t ON u.Username = t.Booked_by_user;

Deleting Entries

DELETE FROM Users;
DELETE FROM Users WHERE Username='user1';

Dropping a Table

DROP TABLE User_Cancellation;
DROP TABLE Users;

Deleting a Database

DROP DATABASE airlineticketingdb;

Conclusion and Key Takeaways

1. SELECT Statements

• Retrieving Data: The SELECT statement is used to query and retrieve data from one or more tables. It allows you to choose specific columns or all columns from a table with the * wildcard.
• Filters and Conditions: Using the WHERE clause, you can filter records based on specific conditions. Logical operators like AND, OR, and NOT enable you to create complex queries. For example:
  • AND ensures that both conditions are true.
  • OR allows for flexibility in conditions by returning results if either condition is true.
• Sorting Results: The ORDER BY clause helps sort the results in ascending (ASC) or descending (DESC) order. This is essential when you need to organize query results, especially when working with large datasets.

2. ALTER TABLE

• Modifying Table Structure: The ALTER TABLE command allows you to modify the structure of an existing table. You can:
  • Add new columns with ADD COLUMN.
  • Modify existing columns (e.g., change their datatype or size) using ALTER COLUMN.
  • Rename columns or tables for better clarity or to follow naming conventions.
• These operations are fundamental for adapting to changing requirements, such as adding new attributes or correcting column types as the database evolves.

3. LIKE Operator

• Pattern Matching: The LIKE operator is used to search for a specified pattern in a column. It is particularly useful when you are not sure of the exact value and want to match based on patterns.
  • The % symbol matches zero or more characters (e.g., LIKE 'user%' finds all usernames starting with ‘user’).
  • The _ symbol matches a single character (e.g., LIKE 'a__le' matches ‘able’, ‘aple’, etc.).
• Using LIKE enables flexible and powerful searches, especially when dealing with partial or uncertain data.

4. Deleting and Dropping Data

• DELETE Command: This is used to remove specific rows from a table that match a condition. It’s important to use the WHERE clause to target specific rows; otherwise, you could delete all rows from the table.
  • Example: DELETE FROM Users WHERE Username = 'user1'; deletes a user with a specific username.
• DROP Command: The DROP command is more destructive as it removes entire tables or even databases from the system. Unlike DELETE, which only removes data, DROP completely eliminates the table structure or database.
  • Example: DROP TABLE Users; deletes the entire Users table, including its data and structure.
• These commands are critical for maintaining database integrity and cleaning up unused or unnecessary data.

5. Building a Strong SQL Foundation

• Data Retrieval and Manipulation: Mastering these commands helps you retrieve, filter, and manipulate data in various ways. This forms the core of SQL operations and allows you to interact with databases effectively.
• Table Management: Understanding how to alter table structures, add or rename columns, and manage data using commands like DELETE and DROP ensures that you can evolve the database schema as required by the application.
• Pattern Matching with LIKE: The ability to search and filter based on patterns with LIKE is essential for flexible querying, especially when working with unstructured or semi-structured data.

Moving Forward:

Understanding and applying these basic SQL commands is the foundation for more complex database operations. In upcoming lessons, you will dive deeper into more advanced concepts such as:

• Joining Multiple Tables: Combining data from multiple tables to create more comprehensive queries.
• Subqueries: Writing queries within queries for more advanced data retrieval techniques.
• Indexes and Performance: Optimizing queries for speed and efficiency.

These concepts will enhance your ability to write complex and optimized SQL queries, which are crucial when working with large, real-world databases.

Lesson 8: Advanced SQL Concepts

Learning Outcomes

• Understand null values in SQL.
• Use aggregate functions in SQL.
• Write nested subqueries.
• Modify databases using SQL.

Null Values in SQL

• NULL values represent missing or unknown information.
• Require special handling in operations:
  • Arithmetic: NULL + 5 = NULL
  • Comparisons: NULL = NULL results in UNKNOWN
  • Logical Operators: NULL AND TRUE = UNKNOWN

Aggregate Functions

SELECT AVG(Salary) FROM Employees;
SELECT COUNT(*) FROM Users;

Grouping Data

SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

Nested Queries

SELECT Name FROM Students WHERE ID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 'CS101');

Modifying Data

Inserting Data

INSERT INTO Courses VALUES ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

Updating Data

UPDATE Employees SET Salary = Salary * 1.05 WHERE Department = 'IT';

Deleting Data

DELETE FROM Employees WHERE Department = 'HR';

Window Functions

SELECT Name, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) FROM Employees;

Next Session: Intermediate and Advanced SQL Topics

• Join Expressions
• Views
• Transactions
• Integrity Constraints
• Index Definition
• Authorization
• Functions and Procedures
• Recursive Queries
• Advanced Aggregation

Conclusion and Key Takeaways

1. Handling NULLs

• NULL Values in SQL: NULL represents missing or unknown data in a database. It’s important to handle NULLs properly because they behave differently in SQL operations.
  • Arithmetic Operations: Any arithmetic operation with NULL will result in NULL (e.g., NULL + 5 = NULL).
  • Comparisons: Comparing NULL with any value (including another NULL) using traditional equality operators results in an unknown outcome, which is why SQL uses IS NULL or IS NOT NULL to check for NULLs.
  • Logical Operators: In logical expressions, NULL behaves differently. For example, NULL AND TRUE = UNKNOWN, so you need to consider how NULL affects your logical conditions.

Properly managing NULL values ensures data integrity and prevents unexpected results in queries, particularly when filtering or grouping data.

2. Aggregate Functions

• Summarizing Data: Aggregate functions allow you to summarize or perform calculations on groups of data. Some commonly used aggregate functions include:
  • AVG(): Calculates the average value of a numeric column.
  • COUNT(): Counts the number of rows in a result set, often used to count records in a table or the number of distinct values.
  • SUM(): Adds up all the values in a numeric column.
  • MIN() and MAX(): Find the minimum and maximum values in a set of data.

Aggregate functions are crucial for analyzing large datasets, such as finding average salaries, counting users, or summing up sales. They are often used in combination with GROUP BY to organize data into meaningful subsets.

3. Nested Queries

• Subqueries: A nested query, or subquery, is a query within another query. Subqueries are particularly useful for performing more complex data retrieval operations where the result of one query serves as the input for another.
  • IN Subquery: For example, you can use a subquery to select records where a value matches any value from another table or query result.
  • Correlated Subquery: This type of subquery references the outer query and is used when comparing each row of the outer query with a result from the subquery.

Nested queries enable you to perform complex filtering and data retrieval tasks, often involving multiple tables or conditions.

4. Window Functions

• Advanced Data Analysis: Window functions provide powerful capabilities for analyzing data across a set of rows that are related to the current row. Unlike aggregate functions, which return a single result for each group, window functions return results for each row within a specified window or partition.
  • RANK(): Assigns a rank to each row within a partition of the result set.
  • ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
  • PARTITION BY: This clause is used to divide the result set into partitions to perform the window function on each partition separately.

Window functions are ideal for ranking data, calculating moving averages, and performing cumulative sums, all without losing row-level detail.

Moving Forward:

These advanced SQL concepts form a strong foundation for tackling complex data challenges. Understanding how to:

• Handle NULL values properly,
• Use aggregate functions to summarize data,
• Write nested queries for more refined data retrieval, and
• Utilize window functions for sophisticated data analysis,

will empower you to handle large, complex datasets efficiently and perform in-depth analysis across multiple tables. These skills are essential for anyone working with real-world databases in industries like finance, marketing, and operations.

In future lessons, you will further enhance your skills with advanced topics such as joins, recursive queries, and optimizations, making you proficient in writing powerful and efficient SQL queries.