Part 3: Advanced SQL Concepts and Commands

In this part of our SQL series, we will delve into more advanced concepts and commands, focusing on the HAVING clause, table-related queries, foreign keys, cascading operations, and altering table schemas. We will also go through examples and demonstrations to ensure a clear understanding of these concepts.

Database and Tables

Let's start by creating a sample database and tables that we will use throughout this part.

Creating the Database and Tables

CREATE DATABASE SchoolDB;
USE SchoolDB;

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  marks INT,
  grade VARCHAR(1),
  city VARCHAR(20)
);

CREATE TABLE dept (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  teacher_name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

Inserting Data into Tables

INSERT INTO student (student_id, name, marks, grade, city) VALUES
(1, 'Alice', 95, 'A', 'New York'),
(2, 'Bob', 88, 'B', 'Los Angeles'),
(3, 'Charlie', 92, 'A', 'Chicago'),
(4, 'David', 76, 'C', 'Houston'),
(5, 'Eve', 85, 'B', 'Phoenix');

INSERT INTO dept (dept_id, dept_name) VALUES
(101, 'Science'),
(102, 'Mathematics'),
(103, 'English');

INSERT INTO teacher (teacher_id, teacher_name, dept_id) VALUES
(201, 'Dr. Smith', 101),
(202, 'Prof. Johnson', 102),
(203, 'Dr. Brown', 103);

Tables Content

student Table

dept Table

teacher Table

HAVING Clause

The HAVING clause is similar to the WHERE clause but is used to apply conditions to groups of rows created by the GROUP BY clause. This is particularly useful when you want to filter groups based on aggregate values.

Example: Count the number of students in each city where the maximum marks exceed 90

SELECT COUNT(name) AS student_count, city
FROM student
GROUP BY city
HAVING MAX(marks) > 90;

Expected Output

General SQL Query Order

SELECT column(s)
FROM table_name
WHERE condition    -- Applies condition on rows | used before GROUP BY
GROUP BY column(s)
HAVING condition   -- Applies condition on groups | used after GROUP BY
ORDER BY column(s) ASC;

Example: Retrieve cities with students having grade "A" and maximum marks above 90

SELECT city
FROM student
WHERE grade = 'A'
GROUP BY city
HAVING MAX(marks) > 90
ORDER BY city DESC;

Expected Output

Table Related Queries

Updating Rows

The UPDATE command is used to modify existing rows in a table. It is essential when data needs to be corrected or updated based on new information.

Example: Update grades from "A" to "O"

SET SQL_SAFE_UPDATES = 0;
UPDATE student
SET grade = 'O'
WHERE grade = 'A';

This command may require disabling SQL safe mode to prevent accidental updates.

Example: Update grades based on marks range

UPDATE student
SET grade = 'B'
WHERE marks BETWEEN 70 AND 80;

This command changes grades to "B" for students with marks between 70 and 80.

Example: Increase marks by 1 for all students

UPDATE student
SET marks = marks + 1;
SELECT * FROM student;

Expected Output

Deleting Rows

The DELETE command is used to remove rows from a table. It is useful for cleaning up obsolete or incorrect data.

Example: Delete students with marks less than 13

DELETE FROM student
WHERE marks < 13;
SELECT * FROM student;

Expected Output

No rows should be deleted as no student has marks less than 13.

Foreign Keys

Foreign keys are used to establish a relationship between two tables. A foreign key in one table points to a primary key in another table.

Example: Creating tables with foreign keys

1. Courses Table

CREATE TABLE dept (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

This table holds department information.

2. Teachers Table

CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  teacher_name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

This table holds teacher information and links each teacher to a department using a foreign key.

Cascading Operations on Foreign Keys

Cascading operations ensure that changes in the parent table are automatically reflected in the child table.

Example: Creating a table with cascading foreign key constraints

CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  teacher_name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);

Demonstration: Impact of Cascading Operations

1. Deleting a Department

DELETE FROM dept
WHERE dept_id = 101;

• This will automatically delete all teachers associated with the department having dept_id = 101.

2. Updating a Department

UPDATE dept
SET dept_name = 'New Science'
WHERE dept_id = 101;

• This will automatically update the dept_id in the teacher table for all teachers associated with the department.

Altering Table Schemas

The ALTER TABLE command is used to modify an existing table structure. It is powerful for adapting to new requirements without recreating the table.

Adding a Column

ALTER TABLE student
ADD COLUMN email VARCHAR(100);

This command adds a new column email to the student table.

Dropping a Column

ALTER TABLE student
DROP COLUMN email;

This command removes the email column from the student table.

Renaming a Table

ALTER TABLE student
RENAME TO pupil;

This command renames the student table to pupil.

Renaming a Column

ALTER TABLE student
CHANGE COLUMN city location VARCHAR(50);

This command renames the city column to location and changes its datatype to VARCHAR(50).

Modifying a Column

ALTER TABLE student
MODIFY grade CHAR(2);

This command changes the datatype of the grade column to CHAR(2).

Exercises with Solutions

Exercise 1: Update Marks and Grades

1. Update the marks of all students by adding 5 to their current marks.

2. Change the grade of all students with marks greater than 85 to "A".

UPDATE student
SET marks = marks + 5;
UPDATE student
SET grade = 'A'
WHERE marks > 85;

Practice Questions

1. Write a query to find the average marks in each city in ascending order.

SELECT city, AVG(marks)
FROM student
GROUP BY city
ORDER BY AVG(marks);

2. Given a table with columns customer_id, customers, mode_of_payments, city, find the total payment according to each payment method.

SELECT mode_of_payments, COUNT(customer_id)
FROM payment_table
GROUP BY mode_of_payments;

By understanding these advanced SQL concepts, you will be able to perform more complex queries and manage your databases more efficiently. In the next part, we will cover more intricate aspects of SQL, including joins, subqueries, and indexes. Stay tuned!