OneCompiler

Indus Refernce DBMS material 2

1665

Practical

--SQL PROBLEM 1

create table Customers
(
customer_id int primary key,
first_name varchar(20)
);

create table Orders
(
order_id int primary key,
amount decimal(10,2)not null,
customer_id int, 
foreign key (customer_id) references Customers(customer_id)
);

insert into customers
values
(1,'c1'),
(2,'c2'),
(3,'c3'),
(4,'c4'),
(5,'c5'),
(6,'c6');

insert into Orders
values
(1001,1234.45,1),
(1002,2234.45,6),
(1003,1334.45,5),
(1004,1434.45,1),
(1005,1634.45,6),
(1006,1834.45,1);

select * from customers;
select * from Orders;

-- PERFORM THE TRANSACTION PART IN THE PROGRAMIZ
START TRANSACTION;

-- Perform some updates or changes
UPDATE Customers SET first_name = 'Updated Name' WHERE customer_id = 1;
UPDATE Orders SET amount = 2000.00 WHERE order_id = 1001;

-- If you decide everything is fine and you want to save the changes
COMMIT;

-- If something goes wrong, you can roll back the changes
-- ROLLBACK;

SQL PROBLEM 2

cover the part of CREATE TABLE , INSERT INTO, CONSTRAINT, FOREIGN KEY, PRIMARY KEY, CREATE VIEW, ALTER TABLE, JOIN , update

-- run this as a whole in one compiler (MYSQL Compiler)

create table Student(
student_id int primary key,
first_name varchar(50),
last_name varchar(50),
email varchar(100),
age int
);

create table Courses(
course_id int primary key,
course_name varchar(100),
duration_in_week int,
fee decimal(10,2)
);

create table enrollments(
enrollment_id int primary key,
student_id int,
course_id int,
enrollment_date DATE,
foreign key (student_id) references Student(student_id),
foreign key (course_id) references Courses(course_id)

);

insert into Student(student_id,first_name,last_name,email,age)
values
(111,'ok ','world','[email protected]', 19),
(222,'which','world','[email protected]',19),
(333,'Nons','world','[email protected]',19);

insert into Courses(course_id,course_name,duration_in_week,fee)
values
(123,'maths',3,50000.00),
(124,'sci',3,12000.00),
(125,'pe',3,12000.00);

insert into enrollments(enrollment_id,student_id,course_id,enrollment_date)
values
(002233,111,123,'2024-11-05'),
(112233,222,124,'2024-11-04'),
(222233,333,125,'2024-11-03');

select*from Student;
select*from Courses;
select*from enrollments;

alter table Student add column gender varchar(20);
update Student set gender='male' where student_id=111;
update Student set gender='male' where student_id=222;
update Student set gender='male' where student_id=333;

select*from Student;

select Student.first_name, Student.last_name, Courses.course_name
from enrollments
JOIN Student on enrollments.student_id = Student.student_id
JOIN Courses on enrollments.course_id  = Courses.course_id;

4. AS alias using in the SQL -https://www.programiz.com/sql/select-as-alias

  • Answer

    create table Customers(
customer_id int primary key,
first_name varchar(100),
last_name varchar(100),
age int,
country varchar(100)
);
insert into Customers(customer_id,first_name,last_name,age,country)
values
(111,'ooook','ahhhh',19,'USA'),
(222,'oook','ahhh',20,'USA'),
(333,'woow','ufff',21,'UK'),
(444,'waah','hook',21,'India'),
(555,'upppp','wow',21,'UK'),
(666,'down','nice',21,'UK');

select*from Customers;

select first_name AS name
FROM Customers;

    output

    image.png

5. PERFORMING ALL JOIN, LEFT, RIGHT, INNER, OUTER

  • Answer

    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary) VALUES
(1, 'John', 'Doe', 1, 50000),
(2, 'Jane', 'Smith', 2, 60000),
(3, 'Emily', 'Johnson', 1, 55000),
(4, 'Michael', 'Brown', 3, 45000),
(5, 'Emma', 'Davis', 2, 70000);

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'Marketing');

select*from Employees;

select* from Departments;

--Inner Join (REMOVE COMMENT BEFORE RUNNING IN ONE COMPILER)
--An INNER JOIN retrieves records that have matching values in both tables. This query retrieves employees and their corresponding department names
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

--2. Left Outer Join
--A LEFT OUTER JOIN returns all records from the left table (Employees), and the matching records from the right table (Departments). If no match is found, NULL is returned for columns from the right table.
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- 3. Right Outer Join
-- A RIGHT OUTER JOIN returns all records from the right table (Departments), and the matching records from the left table (Employees). If no match is found, NULL is returned for columns from the left table.
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
RIGHT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

--4. Full Outer Join
--A FULL OUTER JOIN returns all records when there is a match in either the left or right table. If there is no match, NULL values are returned for non-matching rows from both tables.
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

    image.png