Audi.dbm

CREATE DATABASE Audi
USE Audi
CREATE TABLE Managers (MGR_ID INT PRIMARY KEY, MGR_NAME VARCHAR(50), AGE INT, SEX VARCHAR(10), PLACE VARCHAR(50))
CREATE TABLE Employees (EMP_ID INT PRIMARY KEY, EMP_NAME VARCHAR(50), AGE INT, SEX VARCHAR(10), PLACE VARCHAR(50))
CREATE TABLE Showrooms (SHOWROOM_ID INT PRIMARY KEY, OWNERSHIP VARCHAR(100), LOCATION VARCHAR(50), COUNTRY VARCHAR(50), MGR_ID INT)
CREATE TABLE Divisions (DIVISION_ID INT PRIMARY KEY, DIVISION_NAME VARCHAR(50), MGR_ID INT)
CREATE TABLE Cars (CAR_ID INT PRIMARY KEY, CAR_NAME VARCHAR(50), CAR_TYPE VARCHAR(50), ENGINE_TYPE VARCHAR(50), TRANSMISSION_TYPE VARCHAR(50), FUEL_CAPACITY VARCHAR(10), MILEAGE VARCHAR(10), PRICE VARCHAR(50))
CREATE TABLE Availability (CAR_ID INT PRIMARY KEY, SHOWROOM_ID INT, COUNT INT)
ALTER TABLE Availability ADD FOREIGN KEY(SHOWROOM_ID) REFERENCES Showrooms(SHOWROOM_ID) ON DELETE SET NULL
ALTER TABLE Showrooms ADD FOREIGN KEY(MGR_ID) REFERENCES Managers(MGR_ID) ON DELETE SET NULL
ALTER TABLE Divisions ADD FOREIGN KEY(MGR_ID) REFERENCES Managers(MGR_ID) ON DELETE SET NULL
ALTER TABLE Availability ADD FOREIGN KEY(CAR_ID) REFERENCES Cars(CAR_ID)
INSERT INTO Cars VALUES (1, 'R8', 'Race Car', 'V8+', '6 Speed Manual', 'Petrol', '8.5', '13 Crore')
INSERT INTO Cars VALUES (90, 'E7', 'Sports', 'V9', '7 Speed Manual', 'PetrolXP', '6 Km/L', '15 Crore')
INSERT INTO Cars VALUES (9, 'Chitti', 'Robo', 'Speed1TB', 'Memory1GB', 'Puthiya', 'Etha', 'Bhoomikku')
INSERT INTO Cars VALUES (110, 'rrrr', 'rrrr', 'rrrr', 'rrrr', 'rrrr', 'rrrr', 'rrrr')
DELETE FROM Cars WHERE CAR_ID = 110
DELETE FROM Cars WHERE CAR_ID = 9
INSERT INTO Cars VALUES (101, 'rrrrrr', 'rrrrr', 'rrrrr', 'rrrr', 'rrrr', 'rrrr', 'rrrrr')
DELETE FROM Cars WHERE CAR_ID = 101
DELETE FROM Cars WHERE CAR_ID = 1
INSERT INTO Cars VALUES (55, 'renjish', 'daddyless', 'noengine', '0 trans', 'gay', '-60', '5')
DELETE FROM Cars WHERE CAR_ID = 55
INSERT INTO Cars VALUES (55, 'renjish', 'daddyless', 'adad', 'adsdadsda', 'ss', 'sds', 'sdsd')
DELETE FROM Cars WHERE CAR_ID = 55
INSERT INTO Cars VALUES (1, '23', 'sdaf', 'asdas', 'asd', 'asd', 'ad', 'asd')
DELETE FROM Cars WHERE CAR_ID = 1
INSERT INTO Cars VALUES (121, 'ssda', 'asdas', 'sdasd', 'adas', 'asdasd', 'asda', 'asda')
DELETE FROM Cars WHERE CAR_ID = 121
DELETE FROM Cars WHERE CAR_ID = 90
INSERT INTO Cars VALUES (1, 'grand i10', 'racecar', 'v8', 'manual', 'petrol', '12', '7lakh')
insert into managers values (1,'ash',25,'m','dubai');
INSERT INTO Cars VALUES (5, '1212', 'dsd', 'dfs', 'sdf', 'sdfs', 'df', 'sdf')
INSERT INTO Showrooms VALUES (1, 'Kairali', 'Kerala', 'India', 1)
UPDATE Showrooms SET OWNERSHIP = 'Kairali', LOCATION = 'Tamil Nadu', COUNTRY = 'India', MGR_ID = '1' WHERE SHOWROOM_ID = 1
INSERT INTO Divisions VALUES (1, 'Sales', 1)
TRUNCATE TABLE Divisions
INSERT INTO Managers VALUES (2, 'Navneet', 19, 'M', 'Kochi')
INSERT INTO Employees VALUES (1, 'Navneet', 19, 'M', 'Kochi')
INSERT INTO Employees VALUES (2, 'Navneet', 19, 'M', 'Kochi')
INSERT INTO Cars VALUES (2, 'd', 'csdcsd', 'css', 'cssdc', 'bjkjh', 'jkjk', 'jhkhj')
INSERT INTO Availability VALUES (2, 1, 78)
INSERT INTO Cars VALUES (8, 'sdfsdf', 'dsfd', 'sdf', 'df', 'sdf', 'dsfs', 'sdf')
INSERT INTO Availability VALUES (1, 1, 4)
INSERT INTO Cars VALUES (7, 'ffs', 'dsfsd', 'sdfs', 'sad', 'sada', 'asd', 'asd')
DELETE FROM Availability WHERE CAR_ID = 8
DELETE FROM Cars WHERE CAR_ID = 8
DELETE FROM Employees WHERE EMP_ID = 2
DELETE FROM Managers WHERE MGR_ID = 2
DELETE FROM Employees WHERE EMP_ID = 1
INSERT INTO Managers VALUES (24, 'bit', 19, 'M', 'Kochi')
INSERT INTO Employees VALUES (11, 'teww', 76, 'F', 'Kqweqw eaochi')
INSERT INTO Managers VALUES (234, 'Navasdas neet', 45, 'M', ' dqwer')
INSERT INTO Employees VALUES (118, 'Navnsadwfreet', 21, 'F', 'Khi')
INSERT INTO Cars VALUES (9, 'ds', 'saas', 'asda', 'dfsf', 'dsfs', 'dfs', 'df')
INSERT INTO Availability VALUES (9, 1, 60)
UPDATE Cars SET CAR_NAME = 'grand i10', CAR_TYPE = 'racecar', ENGINE_TYPE = 'v8', TRANSMISSION_TYPE = 'petrol', FUEL_CAPACITY = 'petrol', MILEAGE = '12', PRICE = '7lakh' WHERE CAR_ID = 1
UPDATE Availability SET SHOWROOM_ID = 1, COUNT = 4 WHERE CAR_ID = 1
UPDATE Cars SET CAR_NAME = 'grand i10', CAR_TYPE = 'racecar', ENGINE_TYPE = 'v8', TRANSMISSION_TYPE = 'petrol', FUEL_CAPACITY = 'petrol', MILEAGE = '12', PRICE = '7lakh' WHERE CAR_ID = 1
UPDATE Availability SET SHOWROOM_ID = 1, COUNT = 4 WHERE CAR_ID = 1
UPDATE Cars SET CAR_NAME = 'd', CAR_TYPE = 'csdcsd', ENGINE_TYPE = 'css', TRANSMISSION_TYPE = 'bjkjh', FUEL_CAPACITY = 'bjkjh', MILEAGE = 'jkjk', PRICE = 'jhkhj' WHERE CAR_ID = 2
UPDATE Availability SET SHOWROOM_ID = 1, COUNT = 78 WHERE CAR_ID = 2
UPDATE Cars SET CAR_NAME = 'grand i10', CAR_TYPE = 'racecar', ENGINE_TYPE = 'v8', TRANSMISSION_TYPE = 'manual', FUEL_CAPACITY = 'petrol', MILEAGE = '12', PRICE = '7lakh' WHERE CAR_ID = 1
UPDATE Availability SET SHOWROOM_ID = 1, COUNT = 4 WHERE CAR_ID = 1
DELETE FROM Availability WHERE CAR_ID = 2
DELETE FROM Cars WHERE CAR_ID = 2
INSERT INTO Managers VALUES (235, 'Navneet', 19, 'male', 'Kochi')
INSERT INTO Employees VALUES (119, 'Nikhil', 21, 'male', 'Kochi')
DELETE FROM Availability WHERE CAR_ID = 7
DELETE FROM Cars WHERE CAR_ID = 7
UPDATE Cars SET CAR_NAME = 'waganor', CAR_TYPE = 'aasd', ENGINE_TYPE = 'dfs', TRANSMISSION_TYPE = 'sdf', FUEL_CAPACITY = 'sdfs', MILEAGE = 'df', PRICE = 'sdf' WHERE CAR_ID = 5
UPDATE Availability SET SHOWROOM_ID = 1, COUNT = 70 WHERE CAR_ID = 5
DELETE FROM Availability WHERE CAR_ID = 5
DELETE FROM Cars WHERE CAR_ID = 5
INSERT INTO Employees VALUES (120, 'Gayathri', 20, 'female', 'kochi')
DELETE FROM Employees WHERE EMP_ID = 118
DELETE FROM Employees WHERE EMP_ID = 11
DELETE FROM Managers WHERE MGR_ID = 234
DELETE FROM Managers WHERE MGR_ID = 24
DELETE FROM Availability WHERE CAR_ID = 9
DELETE FROM Cars WHERE CAR_ID = 9
UPDATE Cars SET CAR_NAME = 'grand i10', CAR_TYPE = 'racecar', ENGINE_TYPE = 'v8', TRANSMISSION_TYPE = 'manual', FUEL_CAPACITY = 'petrol', MILEAGE = '12', PRICE = '7lakh' WHERE CAR_ID = 1
UPDATE Availability SET SHOWROOM_ID = 1, COUNT = 25 WHERE CAR_ID = 1