Create Database

CREATE DATABASE IF NOT EXISTS student;

USE student;

Create Table Student_Personal_Info

CREATE TABLE IF NOT EXISTS Student_Personal_Info (

student_id INT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

dob DATE,

address VARCHAR(100)

);

Create Table Student_Academic_Info

CREATE TABLE IF NOT EXISTS Student_Academic_Info (

student_id INT PRIMARY KEY,

subject VARCHAR(50),

marks INT,

grade CHAR(1)

);

Insert 5 Records into Student_Personal_Info

INSERT INTO Student_Personal_Info (student_id, first_name, last_name, dob, address)

VALUES

(1, 'Aarav', 'Sharma', '1990-01-01', '123 Main St'),

(2, 'Ananya', 'Patel', '1992-05-15', '456 Oak St'),

(3, 'Aryan', 'Gupta', '1993-08-20', '789 Pine St'),

(4, 'Ishita', 'Singh', '1995-02-10', '101 Elm St'),

(5, 'Rahul', 'Verma', '1997-04-25', '202 Maple St');

Insert 5 Records into Student_Academic_Info

INSERT INTO Student_Academic_Info (student_id, subject, marks, grade)
VALUES

(1, 'Math', 85, 'A'),

(2, 'English', 92, 'A'),

(3, 'Science', 78, 'B'),

(4, 'History', 95, 'A'),

(5, 'Computer Science', 88, 'B');

ALTER table add foreign key

ALTER TABLE `student_academic_info` ADD CONSTRAINT `fk_student_id` FOREIGN

KEY (`student_id`) REFERENCES `student_personal_info`(`student_id`) ON DELE
TE RESTRICT ON UPDATE RESTRICT;

Add a new column

ALTER TABLE Student_Personal_Info

ADD COLUMN email VARCHAR(100);

Drop the 'address' column

ALTER TABLE Student_Personal_Info

DROP COLUMN address;

Rename the 'dob' column

ALTER TABLE Student_Personal_Info

CHANGE COLUMN dob birthdate DATE;

Modify the datatype of 'birthdate' column

ALTER TABLE Student_Personal_Info

MODIFY COLUMN birthdate DATETIME;

Update the address for a specific student in Student_Personal_Info

UPDATE Student_Personal_Info

SET address = 'New Address'

WHERE student_id = 1;
Update the birthdate for a specific student in Student_Personal_Info
UPDATE Student_Personal_Info

SET birthdate = '1991-02-15'

WHERE student_id = 2;

Update the marks for a specific student and subject in

Student_Academic_Info
UPDATE Student_Academic_Info

SET marks = 90

WHERE student_id = 3 AND subject = 'Math';

Update the grade for a specific student and subject in

Student_Academic_Info
UPDATE Student_Academic_Info

SET grade = 'A+'

WHERE student_id = 4 AND subject = 'History';

Increase marks for all students in Computer Science in

Student_Academic_Info
UPDATE Student_Academic_Info

SET marks = marks + 5

WHERE subject = 'Computer Science';

Update email for a specific student in Student_Personal_Info based on

a pattern
UPDATE Student_Personal_Info

SET email = CONCAT(first_name, '_', last_name, '@newdomain.com')

WHERE student_id = 5;

Display the updated records in Student_Personal_Info

SELECT * FROM Student_Personal_Info;
Display the updated records in Student_Academic_Info
SELECT * FROM Student_Academic_Info;

Display the initial records in Student_Personal_Info

SELECT * FROM Student_Personal_Info;

Truncate the Student_Personal_Info table

TRUNCATE TABLE Student_Personal_Info;

Display the records after truncation

SELECT * FROM Student_Personal_Info;

COMMIT or ROLLBACK
-- Start a transaction explicitly

START TRANSACTION;

-- Update a record in Student_Personal_Info

UPDATE Student_Personal_Info

SET address = 'New Address'

WHERE student_id = 1;

-- Insert a new record in Student_Academic_Info

INSERT INTO Student_Academic_Info (student_id, subject, marks, grade)

VALUES (1, 'History', 88, 'B');

-- Commit the transaction to make the changes permanent

COMMIT;

ROLLBACK

-- Start a transaction explicitly

START TRANSACTION;
-- Update a record in Student_Personal_Info

UPDATE Student_Personal_Info

SET address = 'New Address'

WHERE student_id = 1;

-- Insert a new record in Student_Academic_Info

INSERT INTO Student_Academic_Info (student_id, subject, marks, grade)

VALUES (1, 'History', 88, 'B');

-- Rollback the transaction to undo the changes

ROLLBACK;

Aggregate functions in SQL

1. COUNT(): Returns the number of rows in a set.

2. SUM(): Returns the sum of values in a set.

3. AVG(): Returns the average of values in a set.

4. MIN(): Returns the minimum value in a set.

5. MAX(): Returns the maximum value in a set.

The AS clause in SQL is used to give a column or table a temporary alias, or a different name, in the
result set of a query.

SELECT first_name SELECT first_name AS "First Name", last_name AS "Last Name"

FROM Student_Personal_Info; "First Name", last_name AS "Last Name"

FROM Student_Personal_Info;

Example 1: Count the number of students in Student_Personal_Info

SELECT COUNT(*) AS total_students
FROM Student_Personal_Info;

Example 2: Calculate the average marks in Student_Academic_Info

SELECT AVG(marks) AS average_marks
FROM Student_Academic_Info;

Example 3: Find the highest and lowest marks in Student_Academic_Info

SELECT MAX(marks) AS highest_marks, MIN(marks) AS lowest_marks
FROM Student_Academic_Info;

Example 4: Calculate the total number of students and the average marks in a specific subject
SELECT COUNT(*) AS total_students, AVG(marks) AS average_marks
FROM Student_Academic_Info
WHERE subject = 'Math';

GROUP BY clause in SQL is used to group rows that have the same
values in specified columns into summary rows, like those returned
by aggregate functions.

SELECT column1, column2, ..., aggregate_function(columnX)

FROM table

GROUP BY column1, column2, ...;

breakdown of the key components:

 SELECT clause: Specifies the columns you want to display in the result set.

 FROM clause: Specifies the table from which to retrieve data.

 GROUP BY clause: Lists the columns based on which you want to group the data.

 Aggregate function(s): Perform calculations on the grouped data, like counting, summing,
averaging, finding minimum or maximum, etc.
-- Example: Count the number of students in each subject
SELECT subject, COUNT(*) AS total_students
FROM Student_Academic_Info
GROUP BY subject;

The HAVING clause in SQL is used in conjunction with the

GROUP BY clause to filter the results of a grouped query based on
specified conditions.
SELECT column1, column2, ..., aggregate_function(columnX)

FROM table

GROUP BY column1, column2, ...

HAVING condition;

-- Example: Find subjects with an average marks greater than 85

SELECT subject, AVG(marks) AS average_marks

FROM Student_Academic_Info
GROUP BY subject

HAVING AVG(marks) > 85;

-- Example: Find subjects with more than two students who scored above 90

SELECT subject, COUNT(*) AS total_students_above_90

FROM Student_Academic_Info

WHERE marks > 90

GROUP BY subject

HAVING COUNT(*) > 2;

The ORDER BY clause in SQL is used to sort the result set of a

query in ascending or descending order based on one or more
columns.
SELECT column1, column2, ...
FROM table
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

ASC: Ascending order (default).

DESC: Descending order.

-- Example: Retrieve student names in ascending order based on last names

SELECT first_name, last_name
FROM Student_Personal_Info
ORDER BY last_name DESC;

-- Example: Retrieve student names in ascending order based on last names and first names
SELECT first_name, last_name
FROM Student_Personal_Info
ORDER BY last_name ASC, first_name ASC;

Relational set operator

examples using the "Student_Personal_Info" and "Student_Academic_Info" tables to illustrate
each set operator: UNION, INTERSECT, and EXCEPT.
-- Example: Combine unique student records from both tables with UNION
SELECT student_id, first_name, last_name FROM Student_Personal_Info
UNION
SELECT student_id, first_name, last_name FROM Student_Academic_Info;

INTERSECT:
Retrieve the common student records that exist in both tables.
-- Example: Retrieve common student records between both tables with INTERSECT
SELECT student_id, first_name, last_name FROM Student_Personal_Info
INTERSECT
SELECT student_id, first_name, last_name FROM Student_Academic_Info;

EXCEPT:
Retrieve student records unique to the "Student_Personal_Info" table.
-- Example: Retrieve student records unique to the first table with EXCEPT
SELECT student_id, first_name, last_name FROM Student_Personal_Info
EXCEPT
SELECT student_id, first_name, last_name FROM Student_Academic_Info;

In MySQL, the UNION, INTERSECT, and EXCEPT operators are

handled using UNION, INTERSECT, and MINUS, respectively.
However, it's important to note that MySQL doesn't directly
support INTERSECT and EXCEPT like some other databases do. You
can achieve similar results using different approaches.
Demonstration
-- Drop existing tables if they exist
DROP TABLE IF EXISTS Student_Personal_Info, Student_Academic_Info;

-- Create Student_Personal_Info table with additional columns including 'name'

CREATE TABLE Student_Personal_Info (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
full_name VARCHAR(100),
date_of_birth DATE,
address VARCHAR(100),
email VARCHAR(50)
);

-- Insert sample values into Student_Personal_Info

INSERT INTO Student_Personal_Info (student_id, first_name, last_name, full_name, date_of_birth,
address, email)
VALUES
(1, 'Aarav', 'Sharma', 'Aarav Sharma', '1998-05-15', '123 Main St, City',
'[email protected]'),
(2, 'Ishita', 'Patel', 'Ishita Patel', '1999-08-21', '456 Second St, Town', '[email protected]'),
(3, 'Arjun', 'Verma', 'Arjun Verma', '1997-02-10', '789 Third St, Village',
'[email protected]'),
(4, 'Riya', 'Singh', 'Riya Singh', '1996-11-30', '101 Fourth St, Hamlet', '[email protected]'),
(5, 'Vedansh', 'Gupta', 'Vedansh Gupta', '1998-09-25', '202 Fifth St, Suburb',
'[email protected]');

-- Create Student_Academic_Info table with additional columns including 'name'

CREATE TABLE Student_Academic_Info (
student_id INT PRIMARY KEY,
full_name VARCHAR(100),
subject VARCHAR(50),
marks INT,
grade VARCHAR(2)
);

-- Insert sample values into Student_Academic_Info

INSERT INTO Student_Academic_Info (student_id, full_name, subject, marks, grade)
VALUES
(1, 'Aarav Sharma', 'Math', 85, 'A'),
(2, 'Ishita Patel', 'History', 90, 'B+'),
(3, 'Arjun Verma', 'Science', 78, 'B'),
(4, 'Riya Singh', 'English', 92, 'A+'),
(5, 'Vedansh Gupta', 'Math', 88, 'A');

VIEWs
7.7 VIRTUAL TABLES: CREATING A VIEW (open textbook 2)

CREATE VIEW student_info

as
select student_personal_info.student_id, student_personal_info.first_name,
student_personal_info.last_name, student_personal_info.birthdate, student_personal_info.email,
student_academic_info.subject, student_academic_info.marks, student_academic_info.grade
from student_personal_info
JOIN student_academic_info
on
student_personal_info.student_id=student_academic_info.student_id;

INSERT INTO student_personal_info (student_id, first_name, last_name, birthdate, email)

VALUES (6, 'John', 'Doe', '2000-05-20', '[email protected]');

INSERT INTO student_academic_info (student_id, subject, marks, grade)

VALUES (6, 'Math', 90, 'A');

INSERT INTO student_academic_info (student_id, subject, marks, grade)

VALUES (6, 'Math', 90, 'A');

Stored procedure

5.2 Functions and Procedures (textbook 1)

DELIMITER //

CREATE PROCEDURE GetStudentDetails (IN studentID INT)

BEGIN

SELECT *

FROM student_personal_info

WHERE student_id = studentID;

SELECT *

FROM student_academic_info

WHERE student_id = studentID;

END //

DELIMITER ;

Explanation:

 We define a procedure named "GetStudentDetails" that takes one input parameter

studentID.

 Inside the procedure, we perform two SELECT statements to retrieve the student details
from the "student_personal_info" and "student_academic_info" tables based on the
provided studentID.

 We use the DELIMITER command to change the delimiter temporarily to allow the use of
semicolons within the stored procedure definition.

 Finally, we change the delimiter back to the default (;).

CALL GetStudentDetails(1);