Scribd
Upload
2 views2 pages

SQL Database Creation and Management

The document outlines SQL commands for creating a database and tables for car ownership, including inserting data and establishing foreign key relationships. It also includes SQL queries for retrieving data based on specific conditions, triggers for data validation, and stored procedures for listing cars by purchase date and year. Additionally, it provides MongoDB commands for managing a car collection, including insertion, updating, and deletion of records.

Uploaded by

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

SQL Database Creation and Management

The document outlines SQL commands for creating a database and tables for car ownership, including inserting data and establishing foreign key relationships. It also includes SQL queries for retrieving data based on specific conditions, triggers for data validation, and stored procedures for listing cars by purchase date and year. Additionally, it provides MongoDB commands for managing a car collection, including insertion, updating, and deletion of records.

Uploaded by

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

4 QUESTION

b.
CREATE DATABASE experiment_4;
CONNECT experiment_4;
CREATE TABLE owner (
ssn INT PRIMARY KEY,
name VARCHAR(20),
address VARCHAR(20)
);
CREATE TABLE car (
reg_no INT PRIMARY KEY,
model VARCHAR(10),
color VARCHAR(10),
ssn INT,
dop DATE,
FOREIGN KEY (ssn) REFERENCES owner(ssn)
);
INSERT INTO owner VALUES
(101, 'Owner One', 'Address One'),
(102, 'Owner Two', 'Address Two'),
(103, 'Owner Three', 'Address Three');
INSERT INTO car VALUES
(1001, 'Model One', 'Red', 101, '2022-01-10'),
(1002, 'Model Two', 'Blue', 102, '2023-03-25'),
(1003, 'Model Three', 'Green', 103, '2024-07-15');
c.
SELECT [Link], [Link], COUNT([Link])
FROM owner O, car C
WHERE [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) >=
ALL (
SELECT COUNT([Link])
FROM car M
GROUP BY [Link]
);
d.
SELECT [Link], [Link], [Link], C.reg_no, [Link], [Link], [Link]
FROM owner O, car C
WHERE [Link] = '2011-11-11'
AND [Link] = [Link];
e.
DELIMITER //
CREATE TRIGGER tr4
BEFORE INSERT ON car
FOR EACH ROW
BEGIN
DECLARE cur_date DATE;
SET cur_date = DATE(NOW());
IF [Link] > cur_date THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Incorrect date: Date of purchase cannot be in the
future';
END IF;
END //
DELIMITER ;
f.
date
DELIMITER //
CREATE PROCEDURE list_cars(IN pur_date DATE)
BEGIN
SELECT [Link], [Link], [Link], O.reg_no, [Link], [Link]
FROM owner O, car C
WHERE [Link] = [Link] AND [Link] = pur_date;
END //
DELIMITER ;
-- Procedure for listing car owners and their cars based on a specific purchase
year
DELIMITER //
CREATE PROCEDURE list_cars(IN pur_year INT)
BEGIN
SELECT [Link], [Link], [Link], O.reg_no, [Link], [Link]
FROM owner O, car C
WHERE [Link] = [Link] AND YEAR([Link]) = pur_year;
END //
DELIMITER ;
g.
use experiment_4;
[Link]('car');
[Link]({
reg_no: 1,
model: "Sedan",
color: "Red"
});
[Link]([
{
reg_no: 2,
model: "SUV",
color: "Blue"
},
{
reg_no: 3,
model: "Coupe",
color: "Green"
}
]);
[Link](
{ reg_no: 10 },
{ $set: { reg_no: 20 } }
);
[Link]({ model: "xyz" });
[Link]({ color: "Green" });

You might also like