· 6 years ago · Apr 23, 2019, 04:16 AM
1-- create and select the database
2DROP DATABASE IF EXISTS moore_abigail_assignment6;
3CREATE DATABASE moore_abigail_assignment6;
4USE moore_abigail_assignment6;
5
6-- create the tables
7CREATE TABLE students (
8 studentID INT(11) NOT NULL AUTO_INCREMENT,
9 name VARCHAR(255) NOT NULL,
10 email varchar(255) not null,
11 GPA DECIMAL(4,2) NOT NULL,
12 PRIMARY KEY (studentID)
13);
14
15
16-- insert data into the database
17
18INSERT INTO students VALUES
19(1, 'Po Black', 'poblack@gmail.com', 3.51),
20(2, 'Shifu Hoffman', 'shifuhoffman@gmail.com', 2.52),
21(3, 'Tigress Jolie', 'tigressjolie@gmail.com', 3.63),
22(4, 'Jennifer Yuh', 'jenniferyuh@gmail.com', 1.44),
23(5, 'Ox Storming', 'oxstorming@gmail.com', 3.95),
24(6, 'Monkey Chan', 'monkeychan@gmail.com', 4.00),
25(7, 'Viper Liu', 'viperliu@gmail.com', 2.37),
26(8, 'Mantis Rogan', 'mantisrogan@gmail.com', 3.29),
27(9, 'Crane Cross', 'cranecross@gmail.com', 3.72),
28(10, 'Oogway Kim', 'oogwaykim@gmail.com', 1.53),
29(11, 'Ping Hong', 'pinghong@gmail.com', 2.52);
30
31-- create the users and grant priveleges to those users
32GRANT SELECT, INSERT, DELETE, UPDATE
33ON moore_abigail_assignment6.*
34TO abbieweb@localhost
35IDENTIFIED BY 'abbiechocolate';
36
37GRANT SELECT
38ON students
39TO abbieweb@localhost
40IDENTIFIED BY 'abbiechocolate';