Welcome to the UniversityDB_SQL_Queries repository! This project is designed to give you hands-on experience with SQL queries involving Data Definition Language (DDL) and Data Manipulation Language (DML) operations. The tasks include creating databases and tables, inserting data, and performing various SQL queries to manipulate and retrieve data.
CREATE DATABASE UniversityDB;
USE UniversityDB;
-- Creating the Students table
CREATE TABLE Students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(255),
email_id VARCHAR(255),
GPA DECIMAL(3, 2),
major_subject VARCHAR(255)
);
-- Creating the Courses table
CREATE TABLE Courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(255),
instructor VARCHAR(255),
credits DECIMAL(2, 1)
);
INSERT INTO Students (student_name, email_id, GPA, major_subject) VALUES
('Muhammad Ali', '[email protected]', 3.6, 'Computer Science'),
('Fatima Khan', '[email protected]', 3.9, 'Mathematics'),
('Ahmed Hassan', '[email protected]', 3.2, 'Biology'),
('Ayesha Ahmed', '[email protected]', 3.8, 'Physics'),
('Sana Rehman', '[email protected]', 3.5, 'Chemistry'),
('Bilal Khan', '[email protected]', 3.1, 'Economics'),
('Zara Mahmood', '[email protected]', 3.7, 'Psychology'),
('Hassan Ali', '[email protected]', 3.9, 'English Literature'),
('Aisha Malik', '[email protected]', 3.4, 'Business Administration'),
('Usman Iqbal', '[email protected]', 3.6, 'Political Science');
INSERT INTO Courses (course_name, instructor, credits) VALUES
('Introduction to SQL', 'Dr. Ahmed', 3),
('Data Structures', 'Prof. Fatima', 4),
('Calculus I', 'Dr. Ali', 4),
('Introduction to Biology', 'Prof. Ayesha', 3),
('Financial Accounting', 'Dr. Hassan', 3),
('History of Pakistan', 'Prof. Zara', 3),
('Psychology', 'Dr. Bilal', 4),
('English Literature', 'Prof. Zara', 4),
('Business Management', 'Dr. Usman', 3),
('Computer Networks', 'Prof. Aisha', 3);
SELECT email_id
FROM Students
WHERE GPA < 3.0;
SELECT student_name, GPA
FROM Students
WHERE major_subject = 'Computer Science';
UPDATE Students
SET GPA = 3.9
WHERE student_name = 'Muhammad Ali';
DELETE FROM Students
WHERE GPA = (SELECT MIN(GPA) FROM Students);
SELECT student_name, major_subject
FROM Students
WHERE GPA BETWEEN 3.5 AND 4.0;
SELECT course_name
FROM Courses
WHERE instructor = (SELECT instructor FROM Courses GROUP BY instructor ORDER BY COUNT(*) DESC LIMIT 1);
UPDATE Courses
SET credits = 5
WHERE course_name = 'Data Structures';
DELETE FROM Courses
WHERE credits < 3;
SELECT course_name
FROM Courses
WHERE instructor IN (SELECT instructor FROM Courses GROUP BY instructor HAVING COUNT(*) = 1);
SELECT course_name, instructor
FROM Courses
WHERE credits = (SELECT MAX(credits) FROM Courses);
-
Clone the repository:
git clone https://github.com/Hammad112/UniversityDB_SQL_Queries.git cd UniversityDB_SQL_Queries
-
Set up the database: Execute the provided SQL scripts to create the database, tables, and insert sample data.
-
Run the queries: Use your preferred SQL client or tool to run the provided SQL queries and verify the results.
By engaging with the UniversityDB_SQL_Queries repository, you'll gain valuable hands-on experience in SQL querying, data manipulation, and database management, preparing you for a wide range of data-related tasks in your academic, professional, or personal projects. Happy querying!