Scribd
Upload
9 views

LB Dbms

jhZasjdknakjdnsakjfhdsjkfhl

Uploaded by

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

LB Dbms

jhZasjdknakjdnsakjfhdsjkfhl

Uploaded by

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

LAB SESSIONS

SN Title Signature

1 Introduction And Installation of MY SQL

2 Design , develop and implement the EMPLOYEE


DATABASE specified queries in MySQL

3 Design , develop and implement the ORDER


DATABASE specified queries in MySQL

4 Design , develop and implement the COLLEGE


DATABASE specified queries in MySQL

5 Design , develop and implement the MOVIE


DATABASE specified queries in MySQL
Introduction And Installation of MY SQL

MY SQL
MySQL, standing for "My Structured Query Language," is a
powerful and widely-used open-source relational database
management system (RDBMS). It's a cornerstone technology for
building and managing databases, the backbone of many web
applications and software systems.

Advantages of MySQL:
o Open-source and Free

o Easy to Learn

o Scalable

o Reliable and Secure

o Large Community

Disadvantages of MySQL:
o Limited Functionality

o Not Ideal for Very Large Datasets

o Limited Data Analytics Features


Applications of MySQL
• Web applications
• E-commerce
• CMS (Content Management Systems)
• Social Networking
• Blogging Platforms
• Analytics & Reporting
• Inventory Management
• Financial Applications
INSTALLATION
INSTALLATION COMPLETE
LAB 1

EMPLOYEE DATABASE:
Consider the schema for Company Database:
EMPLOYEE(SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
DEPARTMENT(DNo, DName, MgrSSN, MgrStartDate)
DLOCATION(DNo,DLoc)
PROJECT(PNo, PName, PLocation, DNo)
WORKS_ON(SSN, PNo, Hours)
Write SQL queries to :
1. Make a list of all project numbers for projects that involve an employee
whose last name is ‘Scott’, either as a worker or as a manager of the
department that controls the project.
2. Show the resulting salaries if every employee working on the ‘IoT’ project
is given a 10 percent raise.
3. Find the sum of the salaries of all employees of the ‘Accounts’
department, as well as the maximum salary, the minimum salary, and the
average salary in this department.
4. Retrieve the name of each employee who works on all the projects
controlled by department number 5 (use NOT EXISTS operator).
CREATING AND USING DATABASE

CREATING TABLES
INSERTING DATA INTO TABLE :
USING SELECT QUERY TO VIEW THE VALUES IN THE TABLE

FOR TABLE EMPLOYEES

FOR TABLE DEPARTMENT

FOR TABLE DLOCATION


FOR TABLE PROJECT :

FOR TABLE WORKS_ON :


QUERIES

A. Make a list of all project numbers for projects that involve an


employee whose last name is ‘Scott’, either as a worker or as a
manager of the department that controls the project.
B . Show the resulting salaries if every employee working on the ‘IoT’
project is given a 10 percent raise.

C. Find the sum of the salaries of all employees of the ‘Accounts’


department, as well as the maximum salary, the minimum salary, and
the average salary in this department
D. Retrieve the name of each employee who works on all the projects
controlled by department number 5 (use NOT EXISTS operator)
LAB 2:

Consider the following schema for Order Database:


SALESMAN(Salesman_id, Name, City, Commission)
CUSTOMER(Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS(Ord_No, Purchase_Amt, Ord_Date, Customer_id,
Salesman_id)

Write SQL queries to


a. Count the customers with grades above Bangalore’s average.

b. Find the name and numbers of all salesman who had more than one
customer.

c. List all the salesman and indicate those who have and do not have
customers in their cities (Use UNION operation.)

d. Create a view that finds the salesman who has the customer with the
highest order of a day.

e. Demonstrate the DELETE operation by removing salesman with id


1000. All his orders must also be deleted
QUERIES

CREATING AND USING DATABASE

CREATING TABLES
INSERTING DATA INTO TABLE :
USING SELLECT QUERY TO VIEW THE VALUES IN
THE TABLE

FOR TABLE SALESMAN

FOR TABLE COSTUMER


FOR TABLE ORDERS

A. Count the customers with grades above Bangalore’s


average.
B. Find the name and numbers of all salesman who had more
than one customer.

C. List all the salesman and indicate those who have and do
not have customers in their cities (Use UNION operation.)
D. Create a view that finds the salesman who has the
customer with the highest order of a day.

E. Demonstrate the DELETE operation by removing


salesman with id 1000. All his orders must also be deleted
LAB 3
MOVIE DATABASE:
Consider the schema for Movie Database:
ACTOR(Act_id, Act_Name, Act_Gender)

DIRECTOR(Dir_id, Dir_Name, Dir_Phone)

MOVIES(Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)

MOVIE_CAST(Act_id, Mov_id, Role)

RATING(Mov_id, Rev_Stars)

Write SQL queries to


1. List the titles of all movies directed by ‘Hitchcock’.

2. Find the movie names where one or more actors acted in two or
more movies.

3. List all actors who acted in a movie before 2000 and in a movie after
2015 (use JOIN operation).

4. Find the title of movies and number of stars for each movie that has
at least one rating and find the highest number of stars that movie
received. Sort the result by movie title.

1. Update rating of all movies directed by ‘Steven Spielberg’ to 5


CREATING AND USING DATABASE:

CREATING TABLE:
INSERTING DATA INTO TABLES
USING SELLECT QUERY TO VIEW THE VALUES IN THE
TABLE
FOR TABLE ACTOR:

FOR TABLE DIRECTOR

FOR TABLE MOVIES


FOR TABLE RATING

FOR TABLE MOVIES _CAST


RUNNING QUERIES:
1. List the titles of all movies directed by ‘Hitchcock’.

2. Find the movie names where one or more actors acted in two or
more movies.
3. List all actors who acted in a movie before 2000 and in a movie
after 2015 (use JOIN operation).

4. Find the title of movies and number of stars for each movie
that has at least one rating and find the highest number of stars
that movie received. Sort the result by movie title.
5. Update rating of all movies directed by ‘Steven Spielberg’ to 5
LAB 4 :

Consider the schema for College Database:

STUDENT(USN, SName, Address, Phone, Gender)

SEMSEC(SSID, Sem, Sec) CLASS(USN, SSID)

COURSE(Subcode, Title, Sem, Credits)

IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)

Write SQL queries to


1. List all the student details studying in fourth semester ‘C’
section.

2. Compute the total number of male and female students in each


semester and in each section.

3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in


all Courses.

5. Calculate the FinalIA (average of best two test marks) and update
the corresponding table for all students.
CREATING DATABASE:

CREATING TABLES:
INSERTING DATA INTO TA TABLES
USING SELLECT QUERY TO VIEW THE TABLE:
FOR TABLE STUDENTS

FOR TABLE SEMSEC

FOR TABLE CLASS


FOR TABLE COURSE

FOR TABLE IAMARKS


EXECUTING QUERIES

A. List all the student details studying in fourth


semester ‘C’ section.

B. Compute the total number of male and female


students in each semester and in each section.
C. Create a view of Test1 marks of student USN
‘1BI15CS101’ in all Courses.

D. Calculate the FinalIA (average of best two test


marks) and update the corresponding table for all
students.

You might also like