PREPARATION OF DATABASE

To illustrate different sql commands, following sql commands are first executed to create database tables and to seed the database in
postgresql using psql.

CREATE DATABASE lab_assignment;

USE lab_assignment;
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL, -- Nepali names in English script
age INT NOT NULL,
gender VARCHAR(10),
address VARCHAR(100),
email VARCHAR(50) UNIQUE,
phone_number VARCHAR(15),
enrollment_date DATE,
grade DECIMAL(5,2) CHECK (grade >= 0 AND grade <= 100)
);

CREATE TABLE courses (

course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
course_code VARCHAR(20) UNIQUE NOT NULL,
credits INT CHECK (credits >= 1 AND credits <= 5)
);
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);
INSERT INTO students (student_id, name, age, gender, address, email, phone_number, enrollment_date, grade)
VALUES
(1, 'Suresh Khadka', 20, 'Male', 'Kathmandu', '[email protected]', '9812345678', '2024-01-10', 85.50),
(2, 'Pooja Sharma', 22, 'Female', 'Pokhara', '[email protected]', '9812345679', '2023-06-05', 92.00),
(3, 'Rajesh Shrestha', 23, 'Male', 'Chitwan', '[email protected]', '9812345680', '2024-02-15', 76.25),
(4, 'Anju Rai', 21, 'Female', 'Biratnagar', '[email protected]', '9812345681', '2023-08-25', 88.00);

-- Insert data into `courses` table

INSERT INTO courses (course_id, course_name, course_code, credits)
VALUES
(1, 'Database Systems', 'CS101', 3),
(2, 'Computer Networks', 'CS102', 4),
(3, 'Mathematics for Computer Science', 'CS103', 3),
(4, 'Artificial Intelligence', 'CS104', 3);
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES
(1, 1, '2024-01-15'),
(1, 2, '2024-01-15'),
(2, 3, '2023-06-10'),
(2, 4, '2023-06-10'),
(3, 1, '2024-02-20'),
(3, 4, '2024-02-20'),
(4, 2, '2023-09-05'),
(4, 3, '2023-09-05');
1. Illustration of SELECT, FROM, WHERE clause.
SYNTAX:
SELECT column1, column2, ... FROM <table_name>WHERE condition.

QUERY:
SELECT name, age FROM students WHERE grade > 80;

OUTPUT:

2. Illustration of SELECTING OF ALL COLUMNS.

SYNTAX:
SELECT * FROM table_name;

QUERY:

SELECT * FROM students;

OUTPUT:

3. Illustration of SELECTING OF SPECIFIC COLUMNS.

SYNTAX:
SELECT column1, column2, ...

FROM table_name;

QUERY:

SELECT name, email FROM students;

OUTPUT:
4. Illustration of ORDER BY Clause.
SYNTAX:
SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC or DESC];

QUERY:

SELECT * FROM students ORDER BY grade DESC;

OUTPUT:

5. Illustration of Arithmetic operators.

SYNTAX:

SELECT column1, column2,

(column1 <OPERATOR> column2) AS Add_Result,

FROM table_name;

QUERY:

SELECT name, grade, grade * 1.2 AS adjusted_grade FROM students;

OUTPUT:
6. Illustration of Operator Precedence in Arithmetic expression.
SYNTAX:
SELECT column1, column2,
expression_with_default_precedence AS Result1,
expression_with_parentheses AS Result2
FROM table_name;

QUERY:
SELECT name, (grade + 10) * 1.1 AS new_grade FROM students;

OUTPUT:

7. Illustration of aggregate functions.

SYNTAX:
SELECT
COUNT(column_name) AS Alias1,
SUM(column_name) AS Alias2,
AVG(column_name) AS Alias3,
MIN(column_name) AS Alias4,
MAX(column_name) AS Alias5
FROM table_name;

QUERY:
SELECT AVG(grade) AS average_grade FROM students;
OUTPUT:

8. Illustration of GROUP BY clause

SYNTAX:
SELECT column_name,

aggregate_function(column_name) AS Alias

FROM table_name

GROUP BY column_name;

QUERY:

SELECT course_id, COUNT(student_id) AS num_students

FROM enrollments

GROUP BY course_id;

OUTPUT:

9. Illustration of Restricting Group Results with the HAVING Clause

SYNTAX:

SELECT column_name,
 aggregate_function(column_name) AS Alias

FROM table_name

GROUP BY column_name

HAVING aggregate_function(column_name) condition;

QUERY:

SELECT course_id, COUNT(student_id) AS num_students

FROM enrollments

GROUP BY course_id

HAVING COUNT(student_id) > 1;

OUTPUT:

10. Illustration of Defining a Null Value.

SYNTAX:
SELECT column_name

FROM table_name

WHERE column_name IS NULL;

QUERY:
SELECT name, email FROM students WHERE gender IS NULL;

OUTPUT:
11. Illustration of Using Column Aliases.
SYNTAX:
SELECT column_name AS alias_name
FROM table_name;

QUERY:
SELECT name AS student_name, grade AS final_grade FROM students;

OUTPUT:

12. Illustration of Concatenation Operator

SYNTAX:
SELECT column1 || column2 AS concatenated_column
FROM table_name;

QUERY:
SELECT name || ' → ' || course_name AS course_info
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.course_id;

OUTPUT:
13. Illustration Literal Character Strings.
SYNTAX:
SELECT 'Literal String' AS alias_name;

QUERY:
SELECT name, age, 'Nepali Student' AS description FROM students;

OUTPUT:

14. Illustration of displaying distinct rows.

SYNTAX:
SELECT DISTINCT column_name
FROM table_name;

QUERY:
SELECT DISTINCT grade FROM students;

OUTPUT:
15. Illustration of displaying table structure
SYNTAX:
\d table_name

QUERY:
\d courses

OUTPUT

16. Illustration of Use of BETWEEN Operators.

SYNTAX:
SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

QUERY:
SELECT name FROM students WHERE grade BETWEEN 80 AND 90;

OUTPUT:

17. Illustration of Use of IN Operator

SYNTAX:
SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, ...);

QUERY:
SELECT name FROM students WHERE student_id IN (1, 3, 4);
OUTPUT:

18. Illustration of Using LIKE Operators.

SYNTAX:
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

QUERY:
SELECT name FROM students WHERE name LIKE 'S%';

OUTPUT:

19. Illustration of Using AND Operator.

SYNTAX:
SELECT column_name
FROM table_name
WHERE condition1 AND condition2;

QUERY:
SELECT name FROM students WHERE age > 20 AND grade > 80;

OUTPUT:
20. Illustration of Using OR Operator.
SYNTAX:
SELECT column_name
FROM table_name
WHERE condition1 OR condition2;

QUERY:
SELECT name FROM students WHERE age > 22 OR grade < 80;

OUTPUT:

21. Illustration of Using NOT Operator.

SYNTAX:
SELECT column_name
FROM table_name
WHERE NOT condition;

QUERY:

SELECT name FROM students WHERE NOT (age < 21);

OUTPUT:

22. Illustration of SubQuery.

SYNTAX:
SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

QUERY:
SELECT name FROM students WHERE grade > (SELECT AVG(grade) FROM students);
OUTPUT:

23. Illustration of Creating Cross Joins.

SYNTAX:
SELECT column1, column2
FROM table1
CROSS JOIN table2;

QUERY:
SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;

OUTPUT:
24. Illustration of Natural Join.
SYNTAX:
SELECT column1, column2
FROM table1
NATURAL JOIN table2;

QUERY:
SELECT students.name, courses.course_name
FROM students
NATURAL JOIN enrollments
NATURAL JOIN courses;

OUTPUT:

26. Illustration of Creating Joins with the ON Clause.

SYNTAX:
SELECT column1, column2
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

QUERY:
SELECT students.name, courses.course_name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.course_id;

OUTPUT:
27. Illustration of LEFT OUTER JOIN.
SYNTAX:
SELECT column1, column2
FROM table1
LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

QUERY:
SELECT students.name, courses.course_name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.course_id;

OUTPUT:

28. Illustration of Right OUTER JOIN.

SYNTAX:
SELECT column1, column2
FROM table1
RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;

QUERY:
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN enrollments ON students.student_id = enrollments.student_id
RIGHT JOIN courses ON enrollments.course_id = courses.course_id;

OUTPUT:
29. Illustration of Full OUTER JOIN.
SYNTAX:
SELECT column1, column2
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

QUERY:
SELECT students.name, courses.course_name
FROM students
FULL OUTER JOIN enrollments ON students.student_id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.course_id;

OUTPUT:
30. Illustration of Creating table with enforcement of integrity constraints primary key ,not null unique
,check, Referential integrity.
SYNTAX:
CREATE TABLE table_name (
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype NOT NULL,
column3 datatype UNIQUE,
column4 datatype CHECK (condition),
FOREIGN KEY (column_name) REFERENCES other_table(column_name)
);

QUERY:
CREATE TABLE students_with_constraints (
student_id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
email VARCHAR(50) UNIQUE,
grade NUMERIC(5,2) CHECK (grade >= 0 AND grade <= 100)
);

OUTPUT: