Lab: Practicing Basic SQL Statements in DBMS (PostgreSQL)
Objective: This lab aims to familiarize students with basic SQL
operations in PostgreSQL, including creating tables, inserting data,
updating records, and retrieving data using the SELECT statement.
Understanding SQL Constraints
Primary Key:
A primary key is a unique identifier for each record in a table. It ensures
that each row has a distinct value and cannot be NULL.
Example:
CREATE TABLE Students (
StudentID SERIAL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INTEGER NOT NULL,
Major VARCHAR(50) NOT NULL
);
( پرائمری کیPrimary Key): ( پرائمری کی ایک منفرد شناخت کنندہUnique
Identifier) ( ہے جو ہر ریکارڈ کو جدولTable) میں الگ سے شناخت دیتی ہے۔ یہ یقینی بناتی ہے کہ ہر
( قطارRow) ( کا ایک منفردUnique) نل-( اور نانNon-NULL) ویلیو ہو۔
Foreign Key:
A foreign key is a column that creates a link between two tables. It
ensures referential integrity by enforcing that values in one table
correspond to values in another.
Example:
CREATE TABLE Enrollments (
EnrollmentID SERIAL PRIMARY KEY,
StudentID INTEGER REFERENCES Students(StudentID),
CourseID INTEGER REFERENCES Courses(CourseID)
);
( فارن کیForeign Key): فارن کی ایک ایسا کالم ہوتا ہے جو دو ٹیبلز کے درمیان تعلق قائم کرتا
( ہے۔ یہ ریفرنشل انٹیگریٹیReferential Integrity) کو یقینی بناتا ہے تاکہ ایک ٹیبل میں موجود ویلیوز
( دوسری ٹیبل میں موجود مطابقتیCorresponding) ویلیوز کے ساتھ جڑی ہوں۔
Constraints:
Constraints enforce rules on data columns to maintain data accuracy and
integrity. Examples include:
NOT NULL: Ensures a column cannot have NULL values.
UNIQUE: Ensures all values in a column are unique.
PRIMARY KEY: Uniquely identifies a record.
FOREIGN KEY: Establishes relationships between tables.
CHECK: Ensures values in a column meet a condition.
DEFAULT: Assigns a default value to a column.
مثالیں:
NOT NULL: ( یقینی بناتا ہے کہ کالم میں کوئی خالیNULL) ویلیو نہ ہو۔
UNIQUE: ( کالم میں تمام ویلیوز منفردUnique) ہوں۔
PRIMARY KEY: ٹیبل میں موجود ہر ریکارڈ کی منفرد شناخت کے لیے استعمال ہوتا ہے۔
FOREIGN KEY: مختلف ٹیبلز کے درمیان تعلق قائم کرتا ہے۔
( کس ی کا ل م م ی ں م خ ص و ص ش ر طCondition) کی تصدیق کرتا ہے۔
CHECK:
DEFAULT: ( کسی کالم کے لیے پہلے سے طے شدہDefault) ویلیو فراہم کرتا ہے۔
Example:
CREATE TABLE Courses (
CourseID SERIAL PRIMARY KEY,
CourseName VARCHAR(50) NOT NULL UNIQUE
);
Part 1: Basic Table Operations Without
Constraints
Step 1: Creating a Simple Table (No Constraints)
Create a table named Students with the following attributes:
StudentID (INTEGER)
Name (VARCHAR(50))
Age (INTEGER)
Major (VARCHAR(50))
SQL Commands:
CREATE TABLE Students (
StudentID INTEGER,
Name VARCHAR(50),
Age INTEGER,
Major VARCHAR(50)
);
Step 2: Inserting Data
Insert sample records into the Students table.
SQL Commands:
INSERT INTO Students (StudentID, Name, Age, Major) VALUES
(1, 'Alice Johnson', 20, 'Computer Science'),
(2, 'Bob Smith', 22, 'Data Science'),
(3, 'Charlie Brown', 19, 'Information Technology'),
(4, 'David Lee', 21, 'Software Engineering'),
(5, 'Emma Watson', 23, 'Cyber Security');
Step 3: Updating Data
Modify the age of specific students.
SQL Commands:
UPDATE Students SET Age = 21 WHERE StudentID = 1;
UPDATE Students SET Age = 23 WHERE StudentID = 2;
UPDATE Students SET Major = 'AI & Machine Learning' WHERE
StudentID = 3;
UPDATE Students SET Name = 'Daniel Parker' WHERE StudentID = 4;
UPDATE Students SET Age = 25 WHERE StudentID = 5;
UPDATE Students SET Major = 'Data Analytics' WHERE StudentID = 1;
UPDATE Students SET Age = Age + 1 WHERE Major = 'Computer
Science';
UPDATE Students SET Name = 'Sophia Brown' WHERE Name = 'Charlie
Brown';
UPDATE Students SET Age = 22 WHERE StudentID = 2;
UPDATE Students SET Major = 'Cyber Security' WHERE StudentID = 5;
Step 4: Selecting Data
Retrieve student records.
SQL Commands:
SELECT * FROM Students;
SELECT Name, Age FROM Students WHERE Major = 'Computer Science';
SELECT StudentID, Name FROM Students WHERE Age > 20;
SELECT Name FROM Students WHERE Age BETWEEN 20 AND 25;
SELECT DISTINCT Major FROM Students;
SELECT * FROM Students WHERE Name LIKE 'A%';
SELECT COUNT(*) FROM Students;
SELECT Name, Age FROM Students ORDER BY Age DESC;
SELECT * FROM Students WHERE Major IN ('Data Science', 'Cyber
Security');
SELECT * FROM Students WHERE Age < (SELECT AVG(Age) FROM
Students);
Conclusion: Students should execute each SQL command in their
PostgreSQL environment and observe the results. This lab provides
hands-on experience with basic SQL operations, progressing from
simple tables to structured databases with constraints.
Additional Exercises:
1. Add a new student to the Students table.
2. Add a new course to the Courses table.
3. Enroll an existing student in a new course.
4. Retrieve students along with their enrolled courses.
5. Update a student's major.
6. Delete a student and observe foreign key constraints.
7. Retrieve the total number of students enrolled in each course.
8. Use aggregate functions to find the average age of students.
9. Update course names dynamically using the UPDATE statement.
10. Retrieve all students who have not enrolled in any course.
Part 2: Adding Constraints to Tables
Step 5: Creating a Table with Constraints
Now, create a table with Primary and Foreign Key constraints.
SQL Commands:
CREATE TABLE Students (
StudentID SERIAL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INTEGER NOT NULL,
Major VARCHAR(50) NOT NULL
);
CREATE TABLE Courses (
CourseID SERIAL PRIMARY KEY,
CourseName VARCHAR(50) NOT NULL
);
CREATE TABLE Enrollments (
EnrollmentID SERIAL PRIMARY KEY,
StudentID INTEGER REFERENCES Students(StudentID),
CourseID INTEGER REFERENCES Courses(CourseID)
);
Step 6: Inserting Data into Tables with Constraints
SQL Commands:
INSERT INTO Students (Name, Age, Major) VALUES
('Alice Johnson', 20, 'Computer Science'),
('Bob Smith', 22, 'Data Science');
INSERT INTO Courses (CourseName) VALUES
('Database Management'),
('Machine Learning');
INSERT INTO Enrollments (StudentID, CourseID) VALUES
(1, 1),
(2, 2);
Step 7: Updating Data in Tables with Constraints
SQL Commands:
UPDATE Students SET Age = 21 WHERE StudentID = 1;
UPDATE Courses SET CourseName = 'Advanced DBMS' WHERE CourseID =
1;
