[go: up one dir, main page]
Scribd
Upload
27 views2 pages

Dbms Practice Queries

The document contains practice SQL queries for MySQL/MariaDB, covering SELECT, INSERT, UPDATE, DELETE, and JOIN operations. It includes examples for retrieving student records, counting students by department, adding new entries, updating existing data, and deleting records. Additionally, it demonstrates how to join tables to show relationships between students, courses, and faculty.

Uploaded by

muneebsajjadgondal786
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
27 views2 pages

Dbms Practice Queries

The document contains practice SQL queries for MySQL/MariaDB, covering SELECT, INSERT, UPDATE, DELETE, and JOIN operations. It includes examples for retrieving student records, counting students by department, adding new entries, updating existing data, and deleting records. Additionally, it demonstrates how to join tables to show relationships between students, courses, and faculty.

Uploaded by

muneebsajjadgondal786
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

-- DBMS PRACTICE QUERIES (SELECT, INSERT, UPDATE, DELETE, JOIN)

-- You can copy-paste and practice these in MySQL/MariaDB

-- 1. SELECT Queries --

-- Show all records from Students table


SELECT * FROM Students;

-- Show name and age of students in Computer Science department


SELECT name, age FROM Students WHERE department = 'Computer Science';

-- Show courses taught by 'Dr. Asim'


SELECT course_name FROM Courses c
JOIN Faculty f ON c.faculty_id = f.faculty_id
WHERE f.name = 'Dr. Asim';

-- Count number of students per department


SELECT department, COUNT(*) AS total_students
FROM Students
GROUP BY department;

-- Show students older than 21


SELECT * FROM Students WHERE age > 21;

-- 2. INSERT Queries --

-- Add a new student


INSERT INTO Students (student_id, name, age, department)
VALUES (4, 'Zainab Ali', 23, 'Computer Science');

-- Add a new course


INSERT INTO Courses (course_id, course_name, faculty_id)
VALUES (3, 'Operating Systems', 1);

-- 3. UPDATE Queries --

-- Update a student’s age


UPDATE Students SET age = 24 WHERE student_id = 2;

-- Change course name


UPDATE Courses SET course_name = 'Advanced DBMS' WHERE course_id = 1;

-- 4. DELETE Queries --

-- Delete a student record


DELETE FROM Students WHERE student_id = 4;

-- Delete all assignments with marks below 50


DELETE FROM Assignments WHERE max_marks < 50;

-- 5. JOIN Queries --

-- Show which student is enrolled in which course


SELECT s.name AS student_name, c.course_name
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id;

-- Show each course with its teacher


SELECT c.course_name, f.name AS faculty_name
FROM Courses c
JOIN Faculty f ON c.faculty_id = f.faculty_id;

-- Show each course with its total assignments


SELECT c.course_name, COUNT(a.assignment_id) AS total_assignments
FROM Courses c
LEFT JOIN Assignments a ON c.course_id = a.course_id
GROUP BY c.course_name;

You might also like