DBMS

 https://docs.google.com/spreadsheets/d/1pwujzrd3LeVNe3XLTFjhkRULSw_IwnIkQhCOtblw_Fk/edit?usp=sharing





Drive link :

DBMS pdf link

1. Q1 With the help of customer and order database, implement all types of 

joins.

create database vaishnavi;

show databases;

use vaishnavi;

create table customer(cid int primary key,cname varchar(20) not null,mono double not null);

show tables;

insert into customer values (1,"akku ban",56767),(2,"vaishu hgdg",732383),(3,"pallun frer",675463);

select * from customer;

create table oorder(oid int primary key,odate int not null,oammount int not null,cid int not null);

show tables;

insert into oorder values (1,2019-05-05,55,1),(2,2019-05-06,56,2),(3,2019-05-07,78,1);

select * from oorder;

select * from customer JOIN oorder ON customer.cid=oorder.cid;

select customer.cid,cname,odate from customer JOIN oorder ON customer.cid=oorder.cid;

select * from customer LEFT JOIN oorder ON customer.cid=oorder.cid;

select * from customer RIGHT JOIN oorder ON customer.cid=oorder.cid;

select customer.cname, oorder.oid from customer FULL JOIN oorder ON customer.cid=oorder.cid 

ORDER BY customer.cname;

SELECT customer.cname, oorder.oid

FROM customer

FULL JOIN oorder ON customer.cid = oorder.cid

ORDER BY customer.cname;

Q2With the help of employee and department database, implement all types of joins

create database vaishu;

show databases;

use vaishu;

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

employee_name VARCHAR(255),

department_id INT,

salary DECIMAL(10, 2)

);

select * from employees;

CREATE TABLE departments (

department_id INT PRIMARY KEY,

department_name VARCHAR(255)

);

select * from departments;

-- Insert some sample data

INSERT INTO departments (department_id, department_name) VALUES

(1, 'HR'),

(2, 'Finance'),

(3, 'IT');

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(1, 'John Doe', 1, 50000),

(2, 'Jane Smith', 1, 55000),

(3, 'Bob Johnson', 2, 60000),

(4, 'Alice Williams', 2, 62000),

(5, 'Charlie Brown', 3, 70000);

SELECT *

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

SELECT *

FROM employees

LEFT JOIN departments ON employees.department_id = departments.department_id;

SELECT *

FROM employees

RIGHT JOIN departments ON employees.department_id = departments.department_id;

SELECT * FROM employees

FULL JOIN departments ON employees.department_id = departments.department_id;

Q3 For student and library database, implement all types aggregate functions, having clause, 

group by clause.

create database saty;

show databases;

use saty;

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(255),

age INT,

grade CHAR(1)

);

CREATE TABLE library_books (

book_id INT PRIMARY KEY,

book_title VARCHAR(255),

author VARCHAR(255),

publication_year INT,

available_copies INT

);

-- Insert some sample data

INSERT INTO students (student_id, student_name, age, grade) VALUES

(1, 'Alice Johnson', 18, 'A'),

(2, 'Bob Smith', 17, 'B'),

(3, 'Charlie Brown', 19, 'A'),

(4, 'Diana Miller', 18, 'B'),

(5, 'Ethan Davis', 17, 'A');

select * from students;

INSERT INTO library_books (book_id, book_title, author, publication_year, available_copies) VALUES

(1, 'Introduction to SQL', 'John Doe', 2020, 5),

(2, 'Programming in Python', 'Jane Smith', 2019, 3),

(3, 'Data Structures and Algorithms', 'Chris Evans', 2021, 8),

(4, 'History of Art', 'Emily Johnson', 2018, 2),

(5, 'English Literature Classics', 'Robert White', 2020, 6);

select * from library_books;

/**/

/*COUNT:

Count the number of students.*/

SELECT COUNT(*) AS total_students

FROM students;

/*SUM:

Calculate the total available copies of books in the library.

*/

SELECT SUM(available_copies) AS total_available_copies

FROM library_books;

/*AVG:

Calculate the average age of students.*/

SELECT AVG(age) AS average_age

FROM students;

/*MIN and MAX:

Find the minimum and maximum publication years of books.*/

SELECT MIN(publication_year) AS min_publication_year, MAX(publication_year) AS 

max_publication_year

FROM library_books;

/*HAVING Clause:

Retrieve the grades with an average age greater than 18.*/

SELECT grade, AVG(age) AS average_age

FROM students

GROUP BY grade

HAVING AVG(age) > 18;

/*GROUP BY Clause:

Group students by grade and calculate the total available copies of books for each grade.*/

SELECT s.grade, SUM(l.available_copies) AS total_copies_for_grade

FROM students s

JOIN library_books l ON s.grade = l.grade

GROUP BY s.grade;

Implement a PL/SQL code to print odd numbers from 1 to 50.

DECLARE

BEGIN

FOR i IN 1 .. 50 LOOP IF MOD(i, 2) != 0 THEN

DBMS_OUTPUT.PUT_LINE(i);

END IF;

END LOOP;

END;

1. Q5Implement a PL/SQL code to print Fibonacci series till 50

DECLARE

a NUMBER := 0;

b NUMBER := 1;

c NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE(a);

DBMS_OUTPUT.PUT_LINE(b);

FOR i IN 1..50 LOOP -- Adjust the loop limit to generate more Fibonacci numbers if needed

c := a + b;

DBMS_OUTPUT.PUT_LINE(c);

a := b;

b := c;

END LOOP;

END;

1. Q6Write and implement SQL syntax for the following operations on 

cricket team database i)SELECT ii)ALTER iii)UPDATE iv) DELETE v) 

INSERT

create database ruhi;

show databases;

use ruhi;

create table cricket_team(id int primary key ,player_name varchar(20), age int not null, rrole 

varchar(30), jersey_number int not null);

SELECT * FROM cricket_team;

INSERT INTO cricket_team (id,player_name, age, rrole, jersey_number)

VALUES (1,'Mahi', 40, 'keeper', 7),

(2,'hardik pandya', 31, 'batsman', 33),

(3,'Virat Kohli', 35, 'batsaman', 18),

(4,'shikhar dhawan', 38, 'LH batsman', 78);

SELECT player_name, rrole FROM cricket_team

WHERE rrole = 'Batsman';

ALTER TABLE cricket_team

ADD COLUMN country VARCHAR(255);

UPDATE cricket_team

SET age = 30

WHERE player_name = 'Virat Kohli';

DELETE FROM cricket_team

WHERE player_name = 'Shikhar Dhawan';

DELETE FROM cricket_team

WHERE age > 35;

INSERT INTO cricket_team (player_name, age, rrole, jersey_number)

VALUES ('Rohit Sharma', 34, 'Batsman', 45);

INSERT INTO cricket_team (player_name, age, rrole, jersey_number)

VALUES ('Jasprit Bumrah', 27, 'Bowler', 15),

('Hardik Pandya', 28, 'All-Rounder', 33);

1. Q7 Implement the Views with all operations (i)SELECT ii) ALTER 

iii)UPDATE iv) DELETE v) INSERT)

create database suyash;

show databases;

use suyash;

CREATE TABLE cricket_team (

player_id INT PRIMARY KEY,

player_name VARCHAR(255),

age INT,

role VARCHAR(50),

jersey_number INT,

country VARCHAR(255)

);

-- Insert some sample data

INSERT INTO cricket_team (player_id, player_name, age, rrole, jersey_number, country)

VALUES

(1, 'Virat Kohli', 32, 'Batsman', 18, 'India'),

(2, 'Joe Root', 30, 'Batsman', 66, 'England'),

(3, 'Kane Williamson', 31, 'Batsman', 22, 'New Zealand');

select * from cricket_team;

-- Creating a View

CREATE VIEW cricket_team_view AS

SELECT player_id, player_name, age, rrole, jersey_number, country

FROM cricket_team;

-- Selecting from the View

SELECT * FROM cricket_team_view;

-- Dropping the existing view

DROP VIEW IF EXISTS cricket_team_view;

-- Recreating the view with an additional column

CREATE VIEW cricket_team_view AS

SELECT player_id, player_name, age, rrole, jersey_number, country, batting_average

FROM cricket_team;

-- Updating data through the View

UPDATE cricket_team_view

SET age = 33

WHERE player_name = 'Virat Kohli';

-- Deleting data through the View

DELETE FROM cricket_team_view

WHERE player_name = 'Joe Root';

-- Inserting data into the View

INSERT INTO cricket_team_view (player_name, age, role, jersey_number, country)

VALUES ('Steve Smith', 32, 'Batsman', 49, 'Australia');

1. Q8 Implement the concept of Procedures and its types with suitable 

example

CREATE DEFINER=root@localhost PROCEDURE pro_student()

BEGIN

DECLARE v_ID INT;

DECLARE v_name VARCHAR(255);

DECLARE v_finished INT DEFAULT 0; -- Change 'e' to 0 as the default value

DECLARE c1 CURSOR FOR SELECT ID, name FROM student;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

OPEN c1;

get_student: LOOP

FETCH c1 INTO v_ID, v_name;

IF v_finished = 1 THEN

LEAVE get_student;

END IF;

SELECT CONCAT(v_ID, v_name);

END LOOP get_student;

CLOSE c1;

END

1. Q9 Implement the concept of Functions with suitable example

CREATE DEFINER=root@localhost FUNCTION addition(x int, y int) RETURNS int

DETERMINISTIC

BEGIN

DECLARE result int;

SET result = x + y;

RETURN result;

END

Comments

Post a Comment

Popular posts from this blog

10 Simple Tips to Boost Your Productivity

Image
  In today's fast-paced world, productivity is key to achieving success and staying ahead of the competition. But with so many distractions and demands on our time, it can be challenging to stay focused and productive. Here are ten simple tips to help you boost your productivity and achieve your goals: Set Clear Goals: Before you begin any task, take a few minutes to clarify your goals and define what success looks like. This will help you stay focused and avoid getting sidetracked by irrelevant tasks. Prioritize Your Tasks: Make a to-do list and prioritize your tasks based on their importance and urgency. This will help you stay on track and ensure that you're focusing your time and energy on the most important tasks. Use Time Blocks: Divide your day into time blocks and assign specific tasks to each block. This will help you stay organized and focused, and ensure that you're making progress on your goals. Avoid Multitasking: While it may seem like multitasking is an effic...
Read more