Scribd
Upload
2 views5 pages

DB Ass .SQL

The document outlines the creation and management of three databases: ABD College, ODAAPLC, and Library Management System. It includes SQL commands for creating tables, inserting data, and querying information related to students, courses, instructors, products, customers, and borrowed books. Additionally, it demonstrates the use of foreign keys for establishing relationships between tables and includes various SELECT statements for data retrieval.

Uploaded by

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

DB Ass .SQL

The document outlines the creation and management of three databases: ABD College, ODAAPLC, and Library Management System. It includes SQL commands for creating tables, inserting data, and querying information related to students, courses, instructors, products, customers, and borrowed books. Additionally, it demonstrates the use of foreign keys for establishing relationships between tables and includes various SELECT statements for data retrieval.

Uploaded by

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

CREATE DATABASE ABD College:

USE ABD College:


CREATE TABLE Student (
Unique_ID INT PRIMARY KEY,
Name VARCHAR(10),
DOB DATE,
Gender VARCHAR(10),
Mobile VARCHAR(15)
);
CREATE TABLE Course (
Course_Code VARCHAR(10) PRIMARY KEY,
Course_Name VARCHAR(10),
Credit_Hour INT
);
CREATE TABLE Instructr (
Unique_ID INT PRIMARY KEY,
Name VARCHAR(100),
Gender VARCHAR(10),
Age INT,
Date_of_Hire DATE,
Year_of_Service AS DATEDIFF(YEAR, Date_of_Hire, GETDATE()),
Qualification VARCHAR(50),
Phone VARCHAR(15)
);

CREATE TABLE Enrollment (


Enrollment_ID INT PRIMARY KEY,
Student_ID INT,
Course_Code VARCHAR(10),
Mark FLOAT,
Grade VARCHAR(2),
Registration_Date DATE,
FOREIGN KEY (Student_ID) REFERENCES Student(Unique_ID),
FOREIGN KEY (Course_Code) REFERENCES Course(Course_Code)
);
CREATE TABLE Teaches (
Teaches_ID INT PRIMARY KEY,
Instructor_ID INT,
Course_Code VARCHAR(10),
Total_Hours_Taught INT,
FOREIGN KEY (Instructor_ID) REFERENCES Instructor(Unique_ID),
FOREIGN KEY (Course_Code) REFERENCES Course(Course_Code)
);

INSERT INTO Student(Unique_ID,Name,DOB,Gender,Mobile)


VALUES ('1','Alicejo', '1998/07/21','F','123456789'),
INSERT INTO student values(2,'BobSmith','1997-11-12','M','987654321'),
INSERT INTO student values(3,'CharlieLee','1999-05-05','M','456789123'),
INSERT INTO student values(4,'Diana King','2000-02-28','F','321654987'),
INSERT INTO student values(5,'Evan Davis','1998-09-15','M','654321789');
SELECT * FROM Student;

INSERT INTO course values(101,'DBS',3)


INSERT INTO course values(102,'Programing',3)
INSERT INTO course values(103,'HNS',6)
INSERT INTO course values(104,'Accountig',5)
SELECT * FROM course;

INSERT INTO Instructr values(1,'obsee','f','18','2010/2/1','IT','0912')


INSERT INTO Instructr values(102,'DR TUJI','M','35','2012/7/8','computer scince','091019')
INSERT INTO Instructr values(103,'dr nuhamin','f','25','2013/10/29','medicin','091312')
SELECT * FROM Instructr;

INSERT INTO Enrollment values(01,1,101,89,'A','1998/2/3'),


INSERT INTO Enrollment values(02,2,102,79,'B','1996/1/6'),
INSERT INTO Enrollment values(03,3,103,90,'A','1990/7/9');
SELECT * FROM Enrollment;

INSERT INTO Teaches values(1,1,101,12)


INSERT INTO Teaches values(202,2,102,10)
INSERT INTO Teaches values(203,3,103,8)
SELECT * FROM Teaches;

ALTER TABLE Student ADD Email VARCHAR(100), Address VARCHAR(200);


ALTER TABLE Student DROP COLUMN Mobile;

UPDATE Student SET Name = 'AliceJoh' WHERE Unique_ID = 1;


UPDATE Student SET Name = 'BobSmith' WHERE Unique_ID = 2;

SELECT AVG(Mark) as AverageMark FROM Enrollment;

SELECT s.Unique_ID, [Link], c.Course_Name, [Link], [Link]


FROM Student s
JOIN Enrollment e ON s.Unique_ID = e.Student_ID
JOIN Course c ON e.Course_Code = c.Course_Code;

SELECT [Link], c.Course_Name, [Link], [Link], [Link] AS Instructor


FROM Student s
JOIN Enrollment e ON s.Unique_ID = e.Student_ID
JOIN Course c ON e.Course_Code = c.Course_Code
JOIN Teaches t ON c.Course_Code = t.Course_Code
JOIN Instructor i ON t.Instructor_ID = i.Unique_ID;

ANSWER 2

CREATE DATABASE ODAAPLC;


USE ODAAPLC;
CREATE TABLE Product (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(100),
Quantity INT,
Price DECIMAL(10, 2)
);
CREATE TABLE Customer (
Customer_ID INT PRIMARY KEY,
Customer_Name VARCHAR(100),
Customer_Address VARCHAR(200)
);

CREATE TABLE Orde (


Order_ID INT PRIMARY KEY,
Product_ID INT,
Customer_ID INT,
Order_Quantity INT,
Order_Date DATE,
FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

INSERT INTO Product (Product_ID, Product_Name, Quantity, Price)


VALUES (1, 'Laptop', 50, 999.99),
INSERT INTO Product VALUES(2, 'Mouse', 150, 25.75),
INSERT INTO Product VALUES(3, 'Keyboard', 100, 45.50),
INSERT INTO Product VALUES(4, 'Monitor', 30, 250.00),
INSERT INTO Product VALUES(5, 'Printer', 20, 150.00);
SELECT * FROM Product;

INSERT INTO Customer (Customer_ID, Customer_Name, Customer_Address)


VALUES (1, 'Alice Johnson', '123 Elm Street'),
INSERT INTO Customer VALUES(2, 'Bob Smith', '456 Oak Avenue'),
INSERT INTO Customer VALUES(3, 'Charlie Brown', '789 Pine Road'),
INSERT INTO Customer VALUES(4, 'Diana King', '101 Maple Lane'),
INSERT INTO Customer VALUES(5, 'Evan Davis', '202 Birch Boulevard');
SELECT * FROM Customer;

INSERT INTO Orde (Order_ID, Product_ID, Customer_ID, Order_Quantity, Order_Date)


VALUES (1, 1, 1, 2, '2025-01-15'),
INSERT INTO Orde VALUES(2, 2, 2, 10, '2025-01-16'),
INSERT INTO Orde VALUES(3, 3, 3, 5, '2025-01-17'),
INSERT INTO Orde VALUES(4, 4, 4, 3, '2025-01-18'),
INSERT INTO Orde VALUES(5, 5, 5, 1, '2025-01-19');
SELECT * FROM Orde;

SELECT AVG(Order_Quantity) AS AverageOrder_Quantity FROM Orde;


SELECT o.Order_ID, c.Customer_Name, p.Product_Name, o.Order_Quantity, o.Order_Date
FROM Orde o
JOIN Customer c ON o.Customer_ID = c.Customer_ID
JOIN Product p ON o.Product_ID = p.Product_ID;

SELECT c.Customer_Name, o.Order_ID, p.Product_Name, o.Order_Quantity, o.Order_Date


FROM Customer c
JOIN Orde o ON c.Customer_ID = o.Customer_ID
JOIN Product p ON o.Product_ID = p.Product_ID;

ANSWER number 3
CREATE DATABASE LibraryManagementSystem;
USE LibraryManagementSystem;

CREATE TABLE Student (


StudentNo INT PRIMARY KEY,
Name VARCHAR(100)
);
CREATE TABLE Book (
ISBN VARCHAR(13) PRIMARY KEY,
BookName VARCHAR(100)
);
CREATE TABLE Borrow (
BorrowID INT PRIMARY KEY,
StudentNo INT,
ISBN VARCHAR(13),
DateBorrowed DATE,
ReturnDate DATE,
FOREIGN KEY (StudentNo) REFERENCES Student(StudentNo),
FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);

INSERT INTO Student (StudentNo, Name)


VALUES (1, 'Alice Johnson'),
INSERT INTO Student VALUES(2, 'Bob Smith'),
INSERT INTO Student VALUES(3, 'Charlie Brown'),
INSERT INTO Student VALUES(4, 'Diana King'),
INSERT INTO Student VALUES(5, 'Evan Davis');
SELECT * FROM Student;

INSERT INTO Book (ISBN, BookName)


VALUES ('978-3-16-10-0', 'DBS'),
INSERT INTO Book VALUES('978-0-13-62-7', 'C Programming'),
INSERT INTO Book VALUES('978-0-26-34-8', 'Intro Algorithms'),
INSERT INTO Book VALUES('978-0-07-32-3','Operating System '),
INSERT INTO Book VALUES('978-1-11-26-4', 'Artificial Inte');
SELECT * FROM Book;

INSERT INTO Borrow (BorrowID, StudentNo, ISBN, DateBorrowed, ReturnDate)


VALUES (01, 23, '978-3-26-10-1', '2026-01-01', '2024-01-15'),
INSERT INTO Borrow VALUES(2, 2, '978-0-13-62-7','2025-01-03', '2025-01-17');

SELECT * FROM Borrow;

SELECT COUNT(*) AS TotalBorrowedBooks FROM Borrow;

SELECT [Link], [Link] AS StudentName, [Link], [Link], [Link]


FROM Borrow b
JOIN Student s ON [Link] = [Link]
JOIN Book bk ON [Link] = [Link];

SELECT [Link] AS StudentName, [Link], [Link], [Link], [Link]


FROM Student s
JOIN Borrow b ON [Link] = [Link]
JOIN Book bk ON [Link] = [Link];

You might also like