Scribd
Upload
2 views2 pages

Assignment SQL

The document outlines the creation of a BookStore database with tables for Authors, Books, Customers, and Orders, including their respective fields and relationships. It also provides sample data insertion commands to demonstrate the constraints of the database. Additionally, it includes SQL queries for retrieving specific data subsets from a Titanic dataset.

Uploaded by

manusinghhp7275
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

Assignment SQL

The document outlines the creation of a BookStore database with tables for Authors, Books, Customers, and Orders, including their respective fields and relationships. It also provides sample data insertion commands to demonstrate the constraints of the database. Additionally, it includes SQL queries for retrieving specific data subsets from a Titanic dataset.

Uploaded by

manusinghhp7275
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

Assignment

CREATE DATABASE BookStore;


USE BookStore;

CREATE TABLE Authors (


author_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
nationality VARCHAR(50)
);

CREATE TABLE Books (


ISBN VARCHAR(20) PRIMARY KEY,
title VARCHAR(150) NOT NULL,
author_id INT,
genre VARCHAR(50),
price DECIMAL(10, 2) DEFAULT 0.00,
FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Customers (


customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
address TEXT
);

CREATE TABLE Orders (


order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE DEFAULT (CURRENT_DATE),
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

```
### 2. Sample Data Insertion
```sql
-[span_2](start_span)- Inserting sample data to demonstrate constraints[span_2](end_span)
INSERT INTO Authors VALUES (1, 'J.K. Rowling', 'British');
INSERT INTO Books (ISBN, title, author_id, genre, price)
VALUES ('978-0439708180', 'Harry Potter', 1, 'Fantasy', 29.99);
INSERT INTO Customers VALUES (101, 'Satyam Singh', 'satyam@[Link]', 'Ayodhya,
UP');
INSERT INTO Orders (order_id, customer_id, total_amount) VALUES (1001, 101, 29.99);
Assignment

```
## Part B: Titanic MySQL Query Exercise
Below are the SQL queries designed to retrieve specific data subsets from the Titanic dataset.
| Task | SQL Query |
|---|---|
| 1. All columns | SELECT * FROM Titanic; |
| 2. Distinct Embarked | SELECT DISTINCT Embarked FROM Titanic; |
| 3. Survived Names/Ages | SELECT name, age FROM Titanic WHERE survived = 1; |
| 4. Fare > 60000 | SELECT name, ticket_number FROM Titanic WHERE fare > 60000; |
| 5. Names starting with 'J' | SELECT name, age FROM Titanic WHERE name LIKE 'J%'; |
| 6. Deck 90 or 70 | SELECT name, age FROM Titanic WHERE deck IN (90, 70); |
| 7. Age between 20-30 | SELECT name, age FROM Titanic WHERE age BETWEEN 20 AND
30; |
| 8. Age 20, 25, or 30 | SELECT name, age FROM Titanic WHERE age IN (20, 25, 30); |

You might also like