[go: up one dir, main page]
Scribd
Upload
18 views7 pages

Database lab 4

The document outlines the creation and manipulation of two tables, Orders and Customers, in a database. It includes SQL commands for inserting data, fetching records, and performing various types of joins between the tables. Additionally, it introduces a third table, SHIPS, and demonstrates how to join all three tables to retrieve combined data.

Uploaded by

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

Database lab 4

The document outlines the creation and manipulation of two tables, Orders and Customers, in a database. It includes SQL commands for inserting data, fetching records, and performing various types of joins between the tables. Additionally, it introduces a third table, SHIPS, and demonstrates how to join all three tables to retrieve combined data.

Uploaded by

dua12qureshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

Database lab 4

CREATE TABLE Orders (

customer_id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);

-- insert

INSERT INTO Orders VALUES (0001, 'Clark', 'Sales');

INSERT INTO Orders VALUES (0002, 'Dave', 'Accounting');

INSERT INTO Orders VALUES (0003, 'Ava', 'Sales');

-- fetch

SELECT * FROM Orders;

CREATE TABLE Customers(

customer_id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL,

project TEXT NOT NULL

);

-- insert

INSERT INTO Customers VALUES (0001, 'Clark', 'Sales','software sales');

INSERT INTO Customers VALUES (0002, 'Dave', 'Accounting','HR managment');


INSERT INTO Customers VALUES (0003, 'Ava', 'Sales','product sales');

INSERT INTO Customers VALUES (0004, 'Avas', 'Sales manager','product sales');

-- fetch

SELECT * FROM Customers;

SELECT Customers.customer_id, Orders.name, Customers.project

FROM Orders

INNER JOIN Customers

ON Orders.customer_id=Customers.customer_id;

SELECT Customers.customer_id, Orders.name, Customers.project

FROM Orders

LEFT JOIN Customers

ON Orders.customer_id=Customers.customer_id;

SELECT Customers.customer_id, Orders.name, Customers.project

FROM Orders

RIGHT JOIN Customers

ON Orders.customer_id=Customers.customer_id;

SELECT Customers.customer_id, Orders.name, Customers.project

FROM Orders

CROSS JOIN Customers

ON Orders.customer_id=Customers.customer_id;

+-------------+-------+------------+
| customer_id | name | dept |
+-------------+-------+------------+
| 1 | Clark | Sales |
| 2 | Dave | Accounting |
| 3 | Ava | Sales |
+-------------+-------+------------+
+-------------+-------+---------------+----------------+
| customer_id | name | dept | project |
+-------------+-------+---------------+----------------+
| 1 | Clark | Sales | software sales |
| 2 | Dave | Accounting | HR managment |
| 3 | Ava | Sales | product sales |
| 4 | Avas | Sales manager | product sales |
+-------------+-------+---------------+----------------+
+-------------+-------+----------------+
| customer_id | name | project |
+-------------+-------+----------------+
| 1 | Clark | software sales |
| 2 | Dave | HR managment |
| 3 | Ava | product sales |
+-------------+-------+----------------+
+-------------+-------+----------------+
| customer_id | name | project |
+-------------+-------+----------------+
| 1 | Clark | software sales |
| 2 | Dave | HR managment |
| 3 | Ava | product sales |
+-------------+-------+----------------+
+-------------+-------+----------------+
| customer_id | name | project |
+-------------+-------+----------------+
| 1 | Clark | software sales |
| 2 | Dave | HR managment |
| 3 | Ava | product sales |
| 4 | NULL | product sales |
+-------------+-------+----------------+
+-------------+-------+----------------+
| customer_id | name | project |
+-------------+-------+----------------+
| 1 | Clark | software sales |
| 2 | Dave | HR managment |
| 3 | Ava | product sales |
+-------------+-------+----------------+

MySQL online editor


CREATE TABLE Orders (

customer INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);

-- insert
INSERT INTO Orders VALUES (0001, 'Clark', 'Sales');

INSERT INTO Orders VALUES (0002, 'Dave', 'Accounting');

INSERT INTO Orders VALUES (0003, 'Ava', 'Sales');

-- fetch

SELECT * FROM Orders;

CREATE TABLE Customers(

customer_id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL,

project TEXT NOT NULL

);

-- insert

INSERT INTO Customers VALUES (0001, 'Clark', 'Sales','software sales');

INSERT INTO Customers VALUES (0002, 'Dave', 'Accounting','HR managment');

INSERT INTO Customers VALUES (0003, 'Ava', 'Sales','product sales');

INSERT INTO Customers VALUES (0004, 'Avas', 'Sales manager','product sales');

-- fetch

SELECT * FROM Customers;

SELECT Customers.customer_id, Orders.name, Customers.project

FROM Orders

INNER JOIN Customers

ON Orders.customer=Customers.customer_id;

Output:
+----------+-------+------------+
| customer | name | dept |
+----------+-------+------------+
| 1 | Clark | Sales |
| 2 | Dave | Accounting |
| 3 | Ava | Sales |
+----------+-------+------------+
+-------------+-------+---------------+----------------+
| customer_id | name | dept | project |
+-------------+-------+---------------+----------------+
| 1 | Clark | Sales | software sales |
| 2 | Dave | Accounting | HR managment |
| 3 | Ava | Sales | product sales |
| 4 | Avas | Sales manager | product sales |
+-------------+-------+---------------+----------------+
+-------------+-------+----------------+
| customer_id | name | project |
+-------------+-------+----------------+
| 1 | Clark | software sales |
| 2 | Dave | HR managment |
| 3 | Ava | product sales |
+-------------+-------+----------------+

CREATE TABLE Orders (

customer_id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);

-- insert

INSERT INTO Orders VALUES (0001, 'SPAGHETTI', 'Sales');

INSERT INTO Orders VALUES (0002, 'BIRYANI', 'Accounting');

INSERT INTO Orders VALUES (0003, 'PIZZA', 'Sales');

-- fetch

SELECT * FROM Orders;

CREATE TABLE Customers(


customer_id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL,

project TEXT NOT NULL

);

-- insert

INSERT INTO Customers VALUES (0001, 'Clark', 'Sales','software sales');

INSERT INTO Customers VALUES (0002, 'Dave', 'Accounting','HR managment');

INSERT INTO Customers VALUES (0003, 'Ava', 'Sales','product sales');

INSERT INTO Customers VALUES (0004, 'Avas', 'Sales manager','product sales');

-- fetch

SELECT * FROM Customers;

CREATE TABLE SHIPS(

ship_id INTEGER PRIMARY KEY,

ship_name TEXT NOT NULL

);

INSERT INTO SHIPS VALUES (0001,'A');

INSERT INTO SHIPS VALUES (0002,'B');

INSERT INTO SHIPS VALUES (0003,'C');

INSERT INTO SHIPS VALUES (0004,'D');

INSERT INTO SHIPS VALUES (0005,'E');

INSERT INTO SHIPS VALUES (0006,'F');

SELECT * FROM SHIPS;

-- insert

SELECT Customers.customer_id, Orders.name, Customers.name, SHIPS.ship_name

FROM ((Orders
INNER JOIN Customers ON Orders.customer_id=Customers.customer_id)

INNER JOIN SHIPS ON Orders.customer_id=Customers.customer_id);

Output:

+-------------+-----------+------------+
| customer_id | name | dept |
+-------------+-----------+------------+
| 1 | SPAGHETTI | Sales |
| 2 | BIRYANI | Accounting |
| 3 | PIZZA | Sales |
+-------------+-----------+------------+
+-------------+-------+---------------+----------------+
| customer_id | name | dept | project |
+-------------+-------+---------------+----------------+
| 1 | Clark | Sales | software sales |
| 2 | Dave | Accounting | HR managment |
| 3 | Ava | Sales | product sales |
| 4 | Avas | Sales manager | product sales |
+-------------+-------+---------------+----------------+
+---------+-----------+
| ship_id | ship_name |
+---------+-----------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+---------+-----------+
+-------------+-----------+-------+-----------+
| customer_id | name | name | ship_name |
+-------------+-----------+-------+-----------+
| 3 | PIZZA | Ava | A |
| 2 | BIRYANI | Dave | A |
| 1 | SPAGHETTI | Clark | A |
| 3 | PIZZA | Ava | B |
| 2 | BIRYANI | Dave | B |
| 1 | SPAGHETTI | Clark | B |
| 3 | PIZZA | Ava | C |
| 2 | BIRYANI | Dave | C |
| 1 | SPAGHETTI | Clark | C |
| 3 | PIZZA | Ava | D |
| 2 | BIRYANI | Dave | D |
| 1 | SPAGHETTI | Clark | D |
| 3 | PIZZA | Ava | E |
| 2 | BIRYANI | Dave | E |
| 1 | SPAGHETTI | Clark | E |
| 3 | PIZZA | Ava | F |
| 2 | BIRYANI | Dave | F |
| 1 | SPAGHETTI | Clark | F |
+-------------+-----------+-------+-----------+

You might also like