jRiKFb1g

1-- DROP the database if it exists
2DROP DATABASE IF exists labtest;
3
4-- CREATE THE DATABASE
5CREATE DATABASE labtest;
6
7-- SHOW DATABASES;
8SHOW DATABASES;
9
10-- Use the database
11USE labtest;
12
13-- Create Table 1 
14CREATE TABLE user(
15    userid INT AUTO_INCREMENT PRIMARY KEY,
16    name VARCHAR(50));
17
18-- Create Table 2
19CREATE TABLE sale(
20    saleID  INT AUTO_INCREMENT PRIMARY KEY,
21    userid  INT NOT NULL,
22    item VARCHAR(50),
23    FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE RESTRICT);
24
25-- Create Table 3
26
27SHOW TABLES;
28
29-- INSERT 
30INSERT INTO user(name)
31VALUES ('A'),
32('B'),
33('C'),
34('D'),
35('E');
36
37INSERT INTO sale(userid,item)
38VALUES (2,'item1'),
39(3,'item2'),
40(1,'item3'),
41(4,'item4'),
42(4,'item5');
43
44-- SELECT (READ)
45SELECT saleID,s.userid,name,item
46FROM user as u,sale as s
47WHERE u.userid = s.userid;
48
49
50-- DELETE (DELETE)
51DELETE FROM user
52WHERE name='D';