CS403 Assignment 2 solution july 2023 | DBMS | 100% Solution |

Описание: #CS403AssignmentNo2Spring2023 #CS403 #Assignment2
___________________________________________________________

Instagram:
  / genius_zed  

___________________________________________________________

Task 1:

CREATE DATABASE Bc0000000;

_________________________________

Task 2:

-- Creating Stadiumtable
CREATE TABLE Stadium (
StadiumID INT PRIMARY KEY,
StadiumName VARCHAR(50),
Dimensions INT,
Location VARCHAR(50),
Type VARCHAR(20)
);
-- Creating MatchSchedule Table
CREATE TABLE MatchSchedule (
MatchID INT PRIMARY KEY,
MatchDate DATE,
StartTime TIME,
StadiumID INT,
FOREIGN KEY (StadiumID) REFERENCES Stadium(StadiumID)
);
-- Creating Block table
CREATE TABLE Block (
BlockID INT PRIMARY KEY,
BlockName VARCHAR(20),
Capacity INT,
BlockColor VARCHAR(20),
StadiumID INT,
Charges INT,
FOREIGN KEY (StadiumID) REFERENCES Stadium(StadiumID)
);

______________________________________________________________

TASK: 3

Write SQL Statements to insert 2 records in each table.

Solution:

-- Inserting records into the Stadium table

INSERT INTO Stadium (StadiumID, StadiumName, Dimensions, Location, Type)
VALUES (1, 'Zahoor ilahi', '10000','Gujrat','Cricket');

INSERT INTO Stadium (StadiumID, StadiumName, Dimensions, Location, Type)
VALUES (2, 'Lahore', '40000','Lahore','Cricket');


-- Inserting record into MatchSchedule table

INSERT INTO MatchSchedule (MatchID, MatchDate, StartTime, StadiumID)
VALUES (1, '2023-07-09', '17:30:00', 1);

INSERT INTO MatchSchedule (MatchID, MatchDate, StartTime, StadiumID)
VALUES (2, '2023-07-09', '16:30:00', 2);

-- Inserting record into Blocktable

INSERT INTO Block (BlockID, BlockName, Capacity, BlockColor, StadiumID, Charges)
VALUES(1, 'VIP', 3000, 'GREEN', 1, '5000');

INSERT INTO Block (BlockID, BlockName, Capacity, BlockColor, StadiumID, Charges)
VALUES(2, 'Local', 8000, 'RED', 2, '2000');

___________________________________________________

TASK: 4

1. Display name and capacity of all blocks.
SELECT BlockName, Capacity FROM Block;

2. Display Average charges/amount of blocks using appropriate function.
SELECT AVG(Charges) AS AverageChares From Block;

3. Remove column StadName from Stadium table.
ALTER TABLE `stadium` DROP `StadiumName`;

_______________________________________________

Hashtags:
#CS403Assignment2SolutionSpring2023 #CS403 AssignmentNo2Spring2023 #cs403assignment2 #cs403 #CS403assignment2 #cs403julyassignment2 #cs403Assignment2 #dbms
#CS403AssignmentNo2Spring2023 #CS403 #Assignment2 #Spring2023 #CS403AssignmentSolution #genius_zed