Scribd
Upload
4 views20 pages

Experiment 10

The document outlines SQL queries for creating a database and tables related to a college, including student and faculty tables, along with sample data insertion. It includes tasks to perform various aggregate functions and join operations to analyze student and faculty data. The document also details the results of these queries, demonstrating the use of SQL for data manipulation and retrieval.

Uploaded by

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

Experiment 10

The document outlines SQL queries for creating a database and tables related to a college, including student and faculty tables, along with sample data insertion. It includes tasks to perform various aggregate functions and join operations to analyze student and faculty data. The document also details the results of these queries, demonstrating the use of SQL for data manipulation and retrieval.

Uploaded by

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

Experiment-10

Viswasai Sri Kruthikh Yerramilli –


AP24110011086
BTech 2 nd Year, Sec – ‘G’

Queries on Joining tables and Aggregate Functions

1st Question:
Step 1: Create Database

CREATE DATABASE CollegeDB;

USE CollegeDB;

Step 2: Create Tables

 Student Table

CREATE TABLE Student (

SID INT PRIMARY KEY,

SName VARCHAR(50),

Department VARCHAR(50),

Year INT,

Marks INT

);

 Faculty Table

CREATE TABLE Faculty (

FID INT PRIMARY KEY,

FName VARCHAR(50),

Department VARCHAR(50),

Salary INT

);
Step 3: Insert Sample Data

INSERT INTO Student VALUES

(1, 'Amit', 'CSE', 2, 85),

(2, 'Sneha', 'CSE', 3, 78),

(3, 'Ravi', 'ECE', 2, 90),

(4, 'Priya', 'ECE', 1, 72),

(5, 'Kiran', 'MECH', 3, 88),

(6, 'Anjali', 'CSE', 1, 95),

(7, 'Rahul', 'MECH', 2, 60),

(8, 'Divya', 'ECE', 3, 81);

INSERT INTO Faculty VALUES

(101, 'Dr. Rao', 'CSE', 80000),

(102, 'Dr. Kumar', 'ECE', 75000),

(103, 'Dr. Sharma', 'MECH', 70000),

(104, 'Dr. Reddy', 'CSE', 90000),

(105, 'Dr. Meena', 'ECE', 85000);

Tasks:

1. Find total number of students.

2. Find department-wise student count.

3. Find average marks of students.

4. Find department-wise average marks.

5. Find highest marks scored.

6. Find lowest marks scored.

7. Find total faculty salary.

8. Find department-wise maximum faculty salary.

9. Display departments having average marks greater than 80.

10. Count students scoring above overall average marks.


Answers:

mysql> create database collegedb;

Query OK, 1 row affected (0.04 sec)

mysql>

mysql> use collegedb;

Database changed

mysql>

mysql> create table student (

-> sid int primary key,

-> sname varchar(50),

-> department varchar(50),

-> year int,

-> marks int

-> );

Query OK, 0 rows affected (0.05 sec)

mysql>

mysql> create table faculty (

-> fid int primary key,

-> fname varchar(50),

-> department varchar(50),

-> salary int

-> );

Query OK, 0 rows affected (0.02 sec)


mysql>

mysql> insert into student values

-> (1, 'amit', 'cse', 2, 85),

-> (2, 'sneha', 'cse', 3, 78),

-> (3, 'ravi', 'ece', 2, 90),

-> (4, 'priya', 'ece', 1, 72),

-> (5, 'kiran', 'mech', 3, 88),

-> (6, 'anjali', 'cse', 1, 95),

-> (7, 'rahul', 'mech', 2, 60),

-> (8, 'divya', 'ece', 3, 81);

Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0

mysql>

mysql> insert into faculty values

-> (101, 'dr. rao', 'cse', 80000),

-> (102, 'dr. kumar', 'ece', 75000),

-> (103, 'dr. sharma', 'mech', 70000),

-> (104, 'dr. reddy', 'cse', 90000),

-> (105, 'dr. meena', 'ece', 85000);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql>

mysql> select count(*) as total_students

-> from student;

+----------------+

| total_students |
+----------------+

| 8|

+----------------+

1 row in set (0.01 sec)

mysql>

mysql> select department, count(*) as student_count

-> from student

-> group by department;

+------------+---------------+

| department | student_count |

+------------+---------------+

| cse | 3|

| ece | 3|

| mech | 2|

+------------+---------------+

3 rows in set (0.00 sec)

mysql>

mysql> select avg(marks) as average_marks

-> from student;

+---------------+

| average_marks |

+---------------+

| 81.1250 |

+---------------+

1 row in set (0.00 sec)


mysql>

mysql> select department, avg(marks) as department_average_marks

-> from student

-> group by department;

+------------+--------------------------+

| department | department_average_marks |

+------------+--------------------------+

| cse | 86.0000 |

| ece | 81.0000 |

| mech | 74.0000 |

+------------+--------------------------+

3 rows in set (0.00 sec)

mysql>

mysql> select max(marks) as highest_marks

-> from student;

+---------------+

| highest_marks |

+---------------+

| 95 |

+---------------+

1 row in set (0.00 sec)

mysql>

mysql> select min(marks) as lowest_marks

-> from student;

+--------------+

| lowest_marks |
+--------------+

| 60 |

+--------------+

1 row in set (0.00 sec)

mysql>

mysql> select sum(salary) as total_faculty_salary

-> from faculty;

+----------------------+

| total_faculty_salary |

+----------------------+

| 400000 |

+----------------------+

1 row in set (0.00 sec)

mysql>

mysql> select department, max(salary) as max_faculty_salary

-> from faculty

-> group by department;

+------------+--------------------+

| department | max_faculty_salary |

+------------+--------------------+

| cse | 90000 |

| ece | 85000 |

| mech | 70000 |

+------------+--------------------+

3 rows in set (0.00 sec)


mysql>

mysql> select department, avg(marks) as department_average_marks

-> from student

-> group by department

-> having avg(marks) > 80;

+------------+--------------------------+

| department | department_average_marks |

+------------+--------------------------+

| cse | 86.0000 |

| ece | 81.0000 |

+------------+--------------------------+

2 rows in set (0.00 sec)

mysql>

mysql> select count(*) as students_above_average

-> from student

-> where marks > (select avg(marks) from student);

+------------------------+

| students_above_average |

+------------------------+

| 4|

+------------------------+

1 row in set (0.00 sec)


2nd Question:
Aim: To study and implement different types of SQL JOIN operations.

Theory: A JOIN clause is used to combine rows from two or more tables based

on a related column.

Types of JOIN:

 INNER JOIN

 LEFT JOIN

 RIGHT JOIN

 FULL JOIN

 CROSS JOIN

 SELF JOIN

Step 1: Create Database

CREATE DATABASE CollegeDB;

USE CollegeDB;

Step 2: Create Tables

 Student Table

CREATE TABLE Student (

SID INT PRIMARY KEY,

SName VARCHAR(50),

Department VARCHAR(50),

Year INT

);

 Course Table

CREATE TABLE Course (

CID INT PRIMARY KEY,

CName VARCHAR(50),
Department VARCHAR(50),

Credits INT

);

 Faculty Table

CREATE TABLE Faculty (

FID INT PRIMARY KEY,

FName VARCHAR(50),

Department VARCHAR(50)

);

Step 3: Insert Sample Data

INSERT INTO Student VALUES

(1, 'Amit', 'CSE', 2),

(2, 'Sneha', 'CSE', 3),

(3, 'Ravi', 'ECE', 2),

(4, 'Priya', 'MECH', 1),

(5, 'Kiran', 'ECE', 3);

INSERT INTO Course VALUES

(101, 'DBMS', 'CSE', 4),

(102, 'Networks', 'CSE', 3),

(103, 'VLSI', 'ECE', 4),

(104, 'Thermodynamics', 'MECH', 3);

INSERT INTO Faculty VALUES

(201, 'Dr. Rao', 'CSE'),

(202, 'Dr. Kumar', 'ECE'),

(203, 'Dr. Sharma', 'MECH'),

(204, 'Dr. Reddy', 'CIVIL');


Tasks:

1. Display students along with their department courses. (INNER

JOIN)

2. Display all students and matching courses (if available).( LEFT

JOIN)

3. Display all courses and matching students.( RIGHT JOIN)

4. Display all students and courses (FULL OUTER JOIN)

5. Display all possible combinations of students and courses.( CROSS

JOIN)

6. Find students from the same department.( SELF JOIN)

Answers:
mysql> select [Link], [Link], [Link], [Link]

-> from student s

-> inner join course c

-> on [Link] = [Link];

+-----+-------+------------+----------------+

| sid | sname | department | cname |

+-----+-------+------------+----------------+

| 1 | amit | cse | networks |

| 1 | amit | cse | dbms |

| 2 | sneha | cse | networks |

| 2 | sneha | cse | dbms |

| 3 | ravi | ece | vlsi |

| 4 | priya | mech | thermodynamics |

| 5 | kiran | ece | vlsi |

+-----+-------+------------+----------------+
7 rows in set (0.00 sec)

mysql>

mysql> select [Link], [Link], [Link], [Link]

-> from student s

-> left join course c

-> on [Link] = [Link];

+-----+-------+------------+----------------+

| sid | sname | department | cname |

+-----+-------+------------+----------------+

| 1 | amit | cse | networks |

| 1 | amit | cse | dbms |

| 2 | sneha | cse | networks |

| 2 | sneha | cse | dbms |

| 3 | ravi | ece | vlsi |

| 4 | priya | mech | thermodynamics |

| 5 | kiran | ece | vlsi |

+-----+-------+------------+----------------+

7 rows in set (0.00 sec)

mysql>

mysql> select [Link], [Link], [Link], [Link]

-> from student s

-> right join course c

-> on [Link] = [Link];

+------+-------+----------------+------------+

| sid | sname | cname | department |

+------+-------+----------------+------------+
| 2 | sneha | dbms | cse |

| 1 | amit | dbms | cse |

| 2 | sneha | networks | cse |

| 1 | amit | networks | cse |

| 5 | kiran | vlsi | ece |

| 3 | ravi | vlsi | ece |

| 4 | priya | thermodynamics | mech |

+------+-------+----------------+------------+

7 rows in set (0.00 sec)

mysql>

mysql> select [Link], [Link], [Link], [Link]

-> from student s

-> left join course c

-> on [Link] = [Link]

-> union

-> select [Link], [Link], [Link], [Link]

-> from student s

-> right join course c

-> on [Link] = [Link];

+------+-------+----------------+------------+

| sid | sname | cname | department |

+------+-------+----------------+------------+

| 1 | amit | networks | cse |

| 1 | amit | dbms | cse |

| 2 | sneha | networks | cse |

| 2 | sneha | dbms | cse |

| 3 | ravi | vlsi | ece |


| 4 | priya | thermodynamics | mech |

| 5 | kiran | vlsi | ece |

+------+-------+----------------+------------+

7 rows in set (0.00 sec)

mysql>

mysql> select [Link], [Link]

-> from student s

-> cross join course c;

+-------+----------------+

| sname | cname |

+-------+----------------+

| amit | thermodynamics |

| amit | vlsi |

| amit | networks |

| amit | dbms |

| sneha | thermodynamics |

| sneha | vlsi |

| sneha | networks |

| sneha | dbms |

| ravi | thermodynamics |

| ravi | vlsi |

| ravi | networks |

| ravi | dbms |

| priya | thermodynamics |

| priya | vlsi |

| priya | networks |

| priya | dbms |
| kiran | thermodynamics |

| kiran | vlsi |

| kiran | networks |

| kiran | dbms |

+-------+----------------+

20 rows in set (0.00 sec)

mysql>

mysql> select [Link] as student1, [Link] as student2, [Link]

-> from student s1

-> join student s2

-> on [Link] = [Link]

-> and [Link] <> [Link];

+----------+----------+------------+

| student1 | student2 | department |

+----------+----------+------------+

| sneha | amit | cse |

| amit | sneha | cse |

| kiran | ravi | ece |

| ravi | kiran | ece |

+----------+----------+------------+

4 rows in set (0.00 sec)


3rd Question :

Step 1: Create Database

CREATE DATABASE CollegeDB;

USE CollegeDB;

Step 2: Create Tables

 Student Table

CREATE TABLE Student (

SID INT PRIMARY KEY,

SName VARCHAR(50),

Department VARCHAR(50),

Marks INT

);

 Faculty Table

CREATE TABLE Faculty (

FID INT PRIMARY KEY,

FName VARCHAR(50),

Department VARCHAR(50),

Salary INT

);

Step 3: Insert Sample Data

INSERT INTO Student VALUES

(1, &#39;Amit&#39;, &#39;CSE&#39;, 85),

(2, &#39;Sneha&#39;, &#39;CSE&#39;, 78),

(3, &#39;Ravi&#39;, &#39;ECE&#39;, 90),

(4, &#39;Priya&#39;, &#39;ECE&#39;, 72),

(5, &#39;Kiran&#39;, &#39;MECH&#39;, 88),


(6, &#39;Anjali&#39;, &#39;CSE&#39;, 95),

(7, &#39;Rahul&#39;, &#39;MECH&#39;, 60),

(8, &#39;Divya&#39;, &#39;ECE&#39;, 81);

INSERT INTO Faculty VALUES

(101, &#39;Dr. Rao&#39;, &#39;CSE&#39;, 80000),

(102, &#39;Dr. Kumar&#39;, &#39;ECE&#39;, 75000),

(103, &#39;Dr. Sharma&#39;, &#39;MECH&#39;, 70000),

(104, &#39;Dr. Reddy&#39;, &#39;CSE&#39;, 90000),

(105, &#39;Dr. Meena&#39;, &#39;ECE&#39;, 85000);

Tasks:

1. Display department-wise student count and total faculty salary.

2. Find department-wise average student marks.

3. Display department and maximum faculty salary.

4. Display departments having more than 2 students.

5. Find departments where average marks are greater than 80 and show

faculty count.

6. Display department, minimum marks and maximum salary.

Answers:

mysql> select [Link],

-> count([Link]) as student_count,

-> sum([Link]) as total_faculty_salary

-> from student s

-> join faculty f

-> on [Link] = [Link]

-> group by [Link];


+------------+---------------+----------------------+

| department | student_count | total_faculty_salary |

+------------+---------------+----------------------+

| cse | 6| 510000 |

| ece | 6| 480000 |

| mech | 2| 140000 |

+------------+---------------+----------------------+

3 rows in set (0.00 sec)

mysql>

mysql> select department,

-> avg(marks) as average_marks

-> from student

-> group by department;

+------------+---------------+

| department | average_marks |

+------------+---------------+

| cse | 86.0000 |

| ece | 81.0000 |

| mech | 74.0000 |

+------------+---------------+

3 rows in set (0.00 sec)

mysql>

mysql> select department,

-> max(salary) as maximum_salary

-> from faculty

-> group by department;


+------------+----------------+

| department | maximum_salary |

+------------+----------------+

| cse | 90000 |

| ece | 85000 |

| mech | 70000 |

+------------+----------------+

3 rows in set (0.00 sec)

mysql>

mysql> select department,

-> count(*) as student_count

-> from student

-> group by department

-> having count(*) > 2;

+------------+---------------+

| department | student_count |

+------------+---------------+

| cse | 3|

| ece | 3|

+------------+---------------+

2 rows in set (0.00 sec)

mysql>

mysql> select [Link],

-> count([Link]) as faculty_count

-> from student s

-> join faculty f


-> on [Link] = [Link]

-> group by [Link]

-> having avg([Link]) > 80;

+------------+---------------+

| department | faculty_count |

+------------+---------------+

| cse | 6|

| ece | 6|

+------------+---------------+

2 rows in set (0.00 sec)

mysql>

mysql> select [Link],

-> min([Link]) as minimum_marks,

-> max([Link]) as maximum_salary

-> from student s

-> join faculty f

-> on [Link] = [Link]

-> group by [Link];

+------------+---------------+----------------+

| department | minimum_marks | maximum_salary |

+------------+---------------+----------------+

| cse | 78 | 90000 |

| ece | 72 | 85000 |

| mech | 60 | 70000 |

+------------+---------------+----------------+

3 rows in set (0.00 sec)

You might also like