For this project, we expect you to look at this concept:
Read or watch:
- MySQL cheatsheet
- MySQL Performance: How To Leverage MySQL Database Indexing
- Stored Procedure
- Triggers
- Views
- Functions and Operators
- Trigger Syntax and Examples
- CREATE TABLE Statement
- CREATE PROCEDURE and CREATE FUNCTION Statements
- CREATE INDEX Statement
- CREATE VIEW Statement
Write a SQL script that creates a table users following these requirements:
- With these attributes:
- id, integer, never null, auto increment and primary key
- email, string (255 characters), never null and unique
- name, string (255 characters)
- If the table already exists, your script should not fail
- Your script can be executed on any database
Context: Make an attribute unique directly in the table schema will enforced your business rules and avoid bugs in your application
bob@dylan:~$ echo "SELECT * FROM users;" | mysql -uroot -p holberton
Enter password:
ERROR 1146 (42S02) at line 1: Table 'holberton.users' doesn't exist
bob@dylan:~$
bob@dylan:~$ cat 0-uniq_users.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ echo 'INSERT INTO users (email, name) VALUES ("[email protected]", "Bob");' | mysql -uroot -p holberton
Enter password:
bob@dylan:~$ echo 'INSERT INTO users (email, name) VALUES ("[email protected]", "Sylvie");' | mysql -uroot -p holberton
Enter password:
bob@dylan:~$ echo 'INSERT INTO users (email, name) VALUES ("[email protected]", "Jean");' | mysql -uroot -p holberton
Enter password:
ERROR 1062 (23000) at line 1: Duplicate entry '[email protected]' for key 'email'
bob@dylan:~$
bob@dylan:~$ echo "SELECT * FROM users;" | mysql -uroot -p holberton
Enter password:
id email name
1 [email protected] Bob
2 [email protected] Sylvie
bob@dylan:~$
# Create task files and set execute permission.
touch 0-uniq_users.sql
chmod +x 0-uniq_users.sql
# Tests
cat 0-uniq_users.sql | mysql -uroot -p holbertonWrite a SQL script that creates a table users following these requirements:
- With these attributes: * id, integer, never null, auto increment and primary key * email, string (255 characters), never null and unique * name, string (255 characters) * country, enumeration of countries: US, CO and TN, never null (= default will be the first element of the enumeration, here US)
- If the table already exists, your script should not fail
- Your script can be executed on any database
bob@dylan:~$ echo "SELECT * FROM users;" | mysql -uroot -p holberton
Enter password:
ERROR 1146 (42S02) at line 1: Table 'holberton.users' doesn't exist
bob@dylan:~$
bob@dylan:~$ cat 1-country_users.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ echo 'INSERT INTO users (email, name, country) VALUES ("[email protected]", "Bob", "US");' | mysql -uroot -p holberton
Enter password:
bob@dylan:~$ echo 'INSERT INTO users (email, name, country) VALUES ("[email protected]", "Sylvie", "CO");' | mysql -uroot -p holberton
Enter password:
bob@dylan:~$ echo 'INSERT INTO users (email, name, country) VALUES ("[email protected]", "Jean", "FR");' | mysql -uroot -p holberton
Enter password:
ERROR 1265 (01000) at line 1: Data truncated for column 'country' at row 1
bob@dylan:~$
bob@dylan:~$ echo 'INSERT INTO users (email, name) VALUES ("[email protected]", "John");' | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ echo "SELECT * FROM users;" | mysql -uroot -p holberton
Enter password:
id email name country
1 [email protected] Bob US
2 [email protected] Sylvie CO
3 [email protected] John US
bob@dylan:~$
# Create task files and set execute permission.
touch 1-country_users.sql
chmod +x 1-country_users.sql
# Tests
cat 1-country_users.sql | mysql -uroot -p holbertonWrite a SQL script that ranks country origins of bands, ordered by the number of (non-unique) fans
Requirements:
- Import this table dump: metal_bands.sql.zip
- Column names must be: origin and nb_fans
- Your script can be executed on any database
Context: Calculate/compute something is always power intensive… better to distribute the load!
bob@dylan:~$ cat metal_bands.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 2-fans.sql | mysql -uroot -p holberton > tmp_res ; head tmp_res
Enter password:
origin nb_fans
USA 99349
Sweden 47169
Finland 32878
United Kingdom 32518
Germany 29486
Norway 22405
Canada 8874
The Netherlands 8819
Italy 7178
bob@dylan:~$
# Create task files and set execute permission.
touch 2-fans.sql
chmod +x 2-fans.sql
# Tests
cat 2-fans.sql | mysql -uroot -p holberton > tmp_res ; head tmp_resWrite a SQL script that lists all bands with Glam rock as their main style, ranked by their longevity
Requirements:
- Import this table dump: metal_bands.sql.zip
- Column names must be: band_name and lifespan (in years)
- You should use attributes formed and split for computing the lifespan
- Your script can be executed on any database
bob@dylan:~$ cat metal_bands.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 3-glam_rock.sql | mysql -uroot -p holberton
Enter password:
band_name lifespan
Alice Cooper 56
Mötley Crüe 34
Marilyn Manson 31
The 69 Eyes 30
Hardcore Superstar 23
Nasty Idols 0
Hanoi Rocks 0
bob@dylan:~$
# Create task files and set execute permission.
touch 3-glam_rock.sql
chmod +x 3-glam_rock.sql
cat 3-glam_rock.sql | mysql -uroot -p holbertonDefine and annotate the following variables with the specified values:
- a, an integer with a value of 1
- pi, a float with a value of 3.14
- i_understand_annotations, a boolean with a value of True
- school, a string with a value of “Holberton”
bob@dylan:~$ cat 4-store.sql
#!/usr/bin/env python3
a = __import__('4-define_variables').a
pi = __import__('4-define_variables').pi
i_understand_annotations = __import__('4-define_variables').i_understand_annotations
school = __import__('4-define_variables').school
print("a is a {} with a value of {}".format(type(a), a))
print("pi is a {} with a value of {}".format(type(pi), pi))
print("i_understand_annotations is a {} with a value of {}".format(type(i_understand_annotations), i_understand_annotations))
print("school is a {} with a value of {}".format(type(school), school))
bob@dylan:~$ ./4-store.sql
a is a <class 'int'> with a value of 1
pi is a <class 'float'> with a value of 3.14
i_understand_annotations is a <class 'bool'> with a value of True
school is a <class 'str'> with a value of Holberton
# Create task files and set execute permission.
touch 4-store.sql
chmod +x 4-store.sql
cat 4-main.sql | mysql -uroot -p holberton
# Tests
touch 4-init.sql
chmod +x 4-init.sql
cat 4-init.sql | mysql -uroot -p holberton Write a SQL script that creates a trigger that resets the attribute valid_email only when the email has been changed.
Context: Nothing related to MySQL, but perfect for user email validation - distribute the logic to the database itself!
bob@dylan:~$ cat 5-init.sql
-- Initial
DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
id int not null AUTO_INCREMENT,
email varchar(255) not null,
name varchar(255),
valid_email boolean not null default 0,
PRIMARY KEY (id)
);
INSERT INTO users (email, name) VALUES ("[email protected]", "Bob");
INSERT INTO users (email, name, valid_email) VALUES ("[email protected]", "Sylvie", 1);
INSERT INTO users (email, name, valid_email) VALUES ("[email protected]", "Jeanne", 1);
bob@dylan:~$
bob@dylan:~$ cat 5-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 5-valid_email.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 5-main.sql
Enter password:
-- Show users and update (or not) email
SELECT * FROM users;
UPDATE users SET valid_email = 1 WHERE email = "[email protected]";
UPDATE users SET email = "[email protected]" WHERE email = "[email protected]";
UPDATE users SET name = "Jannis" WHERE email = "[email protected]";
SELECT "--";
SELECT * FROM users;
UPDATE users SET email = "[email protected]" WHERE email = "[email protected]";
SELECT "--";
SELECT * FROM users;
bob@dylan:~$
bob@dylan:~$ cat 5-main.sql | mysql -uroot -p holberton
Enter password:
id email name valid_email
1 [email protected] Bob 0
2 [email protected] Sylvie 1
3 [email protected] Jeanne 1
--
--
id email name valid_email
1 [email protected] Bob 1
2 [email protected] Sylvie 0
3 [email protected] Jannis 1
--
--
id email name valid_email
1 [email protected] Bob 1
2 [email protected] Sylvie 0
3 [email protected] Jannis 1
bob@dylan:~$
# Create task files and set execute permission.
touch 5-valid_email.sql
chmod +x 5-valid_email.sql
cat 5-init.sql | mysql -uroot -p holberton
# Tests
touch 5-init.sql
chmod +x 5-init.sql
cat 5-valid_email.sql | mysql -uroot -p holberton
6. Add bonus 6-bonus.sql
Write a SQL script that creates a stored procedure AddBonus that adds a new correction for a student.
Requirements:
- Procedure AddBonus is taking 3 inputs (in this order): * user_id, a users.id value (you can assume user_id is linked to an existing users) * project_name, a new or already exists projects - if no projects.name found in the table, you should create it * score, the score value for the correction
Context: Write code in SQL is a nice level up!
bob@dylan:~$ cat 6-init.sql
-- Initial
DROP TABLE IF EXISTS corrections;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
CREATE TABLE IF NOT EXISTS users (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
average_score float default 0,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS projects (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS corrections (
user_id int not null,
project_id int not null,
score int default 0,
KEY `user_id` (`user_id`),
KEY `project_id` (`project_id`),
CONSTRAINT fk_user_id FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT fk_project_id FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
);
INSERT INTO users (name) VALUES ("Bob");
SET @user_bob = LAST_INSERT_ID();
INSERT INTO users (name) VALUES ("Jeanne");
SET @user_jeanne = LAST_INSERT_ID();
INSERT INTO projects (name) VALUES ("C is fun");
SET @project_c = LAST_INSERT_ID();
INSERT INTO projects (name) VALUES ("Python is cool");
SET @project_py = LAST_INSERT_ID();
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_c, 80);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_py, 96);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_c, 91);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_py, 73);
bob@dylan:~$
bob@dylan:~$ cat 6-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 6-bonus.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 6-main.sql
Enter password:
-- Show and add bonus correction
SELECT * FROM projects;
SELECT * FROM corrections;
SELECT "--";
CALL AddBonus((SELECT id FROM users WHERE name = "Jeanne"), "Python is cool", 100);
CALL AddBonus((SELECT id FROM users WHERE name = "Jeanne"), "Bonus project", 100);
CALL AddBonus((SELECT id FROM users WHERE name = "Bob"), "Bonus project", 10);
CALL AddBonus((SELECT id FROM users WHERE name = "Jeanne"), "New bonus", 90);
SELECT "--";
SELECT * FROM projects;
SELECT * FROM corrections;
bob@dylan:~$
bob@dylan:~$ cat 6-main.sql | mysql -uroot -p holberton
Enter password:
id name
1 C is fun
2 Python is cool
user_id project_id score
1 1 80
1 2 96
2 1 91
2 2 73
--
--
--
--
id name
1 C is fun
2 Python is cool
3 Bonus project
4 New bonus
user_id project_id score
1 1 80
1 2 96
2 1 91
2 2 73
2 2 100
2 3 100
1 3 10
2 4 90
bob@dylan:~$
# Create task files and set execute permission.
touch 6-bonus.sql
chmod +x 6-bonus.sql
cat 6-init.sql | mysql -uroot -p holberton
# Tests
touch 6-init.sql
chmod +x 6-init.sql
cat 6-bonus.sql | mysql -uroot -p holberton
# Lint
pycodestyle 6-bonus.sql
mypy 6-bonus.sqlWrite a SQL script that creates a stored procedure ComputeAverageScoreForUser that computes and store the average score for a student. Note: An average score can be a decimal
Requirements:
- Procedure ComputeAverageScoreForUser is taking 1 input: * user_id, a users.id value (you can assume user_id is linked to an existing users)
bob@dylan:~$ cat 7-init.sql
-- Initial
DROP TABLE IF EXISTS corrections;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
CREATE TABLE IF NOT EXISTS users (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
average_score float default 0,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS projects (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS corrections (
user_id int not null,
project_id int not null,
score int default 0,
KEY `user_id` (`user_id`),
KEY `project_id` (`project_id`),
CONSTRAINT fk_user_id FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT fk_project_id FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
);
INSERT INTO users (name) VALUES ("Bob");
SET @user_bob = LAST_INSERT_ID();
INSERT INTO users (name) VALUES ("Jeanne");
SET @user_jeanne = LAST_INSERT_ID();
INSERT INTO projects (name) VALUES ("C is fun");
SET @project_c = LAST_INSERT_ID();
INSERT INTO projects (name) VALUES ("Python is cool");
SET @project_py = LAST_INSERT_ID();
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_c, 80);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_py, 96);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_c, 91);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_py, 73);
bob@dylan:~$
bob@dylan:~$ cat 7-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 7-average_score.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 7-main.sql
-- Show and compute average score
SELECT * FROM users;
SELECT * FROM corrections;
SELECT "--";
CALL ComputeAverageScoreForUser((SELECT id FROM users WHERE name = "Jeanne"));
SELECT "--";
SELECT * FROM users;
bob@dylan:~$
bob@dylan:~$ cat 7-main.sql | mysql -uroot -p holberton
Enter password:
id name average_score
1 Bob 0
2 Jeanne 0
user_id project_id score
1 1 80
1 2 96
2 1 91
2 2 73
--
--
--
--
id name average_score
1 Bob 0
2 Jeanne 82
bob@dylan:~$
# Create task files and set execute permission.
touch 7-average_score.sql
chmod +x 7-average_score.sql
# Tests
touch 7-init.sql
chmod +x 7-init.sql
# Lint
pycodestyle 7-average_score.sql
mypy 7-average_score.sqlWrite a SQL script that creates an index idx_name_first on the table names and the first letter of name.
Requirements:
- Import this table dump: names.sql.zip
- Only the first letter of name must be indexed
Context: Index is not the solution for any performance issue, but well used, it’s really powerful!
bob@dylan:~$ cat names.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ mysql -uroot -p holberton
Enter password:
mysql> SELECT COUNT(name) FROM names WHERE name LIKE 'a%';
+-------------+
| COUNT(name) |
+-------------+
| 302936 |
+-------------+
1 row in set (2.19 sec)
mysql>
mysql> exit
bye
bob@dylan:~$
bob@dylan:~$ cat 8-index_my_names.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ mysql -uroot -p holberton
Enter password:
mysql> SHOW index FROM names;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| names | 1 | idx_name_first | 1 | name | A | 25 | 1 | NULL | YES | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT COUNT(name) FROM names WHERE name LIKE 'a%';
+-------------+
| COUNT(name) |
+-------------+
| 302936 |
+-------------+
1 row in set (0.82 sec)
mysql>
mysql> exit
bye
bob@dylan:~$
# Create task files and set execute permission.
touch 8-index_my_names.sql
chmod +x 8-index_my_names.sqlWrite a SQL script that creates an index idx_name_first_score on the table names and the first letter of name and the score.
Requirements:
- Import this table dump: names.sql.zip
- Only the first letter of name AND score must be indexed
bob@dylan:~$ cat names.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ mysql -uroot -p holberton
Enter password:
mysql> SELECT COUNT(name) FROM names WHERE name LIKE 'a%' AND score < 80;
+-------------+
| count(name) |
+-------------+
| 60717 |
+-------------+
1 row in set (2.40 sec)
mysql>
mysql> exit
bye
bob@dylan:~$
bob@dylan:~$ cat 9-index_name_score.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ mysql -uroot -p holberton
Enter password:
mysql> SHOW index FROM names;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| names | 1 | idx_name_first_score | 1 | name | A | 25 | 1 | NULL | YES | BTREE | | |
| names | 1 | idx_name_first_score | 2 | score | A | 3901 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql>
mysql> SELECT COUNT(name) FROM names WHERE name LIKE 'a%' AND score < 80;
+-------------+
| COUNT(name) |
+-------------+
| 60717 |
+-------------+
1 row in set (0.48 sec)
mysql>
mysql> exit
bye
bob@dylan:~$
# Create task files and set execute permission.
touch 9-index_name_score.sql
chmod +x 9-index_name_score.sql
# Tests
touch 9-main.py
chmod +x 9-main.py
./9-main.pyWrite a SQL script that creates a function SafeDiv that divides (and returns) the first by the second number or returns 0 if the second number is equal to 0.
Requirements:
- You must create a function
- The function SafeDiv takes 2 arguments: * a, INT * b, INT
- And returns a / b or 0 if b == 0
bob@dylan:~$ cat 10-init.sql
-- Initial
DROP TABLE IF EXISTS numbers;
CREATE TABLE IF NOT EXISTS numbers (
a int default 0,
b int default 0
);
INSERT INTO numbers (a, b) VALUES (10, 2);
INSERT INTO numbers (a, b) VALUES (4, 5);
INSERT INTO numbers (a, b) VALUES (2, 3);
INSERT INTO numbers (a, b) VALUES (6, 3);
INSERT INTO numbers (a, b) VALUES (7, 0);
INSERT INTO numbers (a, b) VALUES (6, 8);
bob@dylan:~$ cat 10-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 10-div.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ echo "SELECT (a / b) FROM numbers;" | mysql -uroot -p holberton
Enter password:
(a / b)
5.0000
0.8000
0.6667
2.0000
NULL
0.7500
bob@dylan:~$
bob@dylan:~$ echo "SELECT SafeDiv(a, b) FROM numbers;" | mysql -uroot -p holberton
Enter password:
SafeDiv(a, b)
5
0.800000011920929
0.6666666865348816
2
0
0.75
bob@dylan:~$
# Create task files and set execute permission.
touch 10-div.sql
chmod +x 10-div.sql
# Tests
touch 10-init.sql
chmod +x 10-init.sqlWrite a SQL script that creates a view need_meeting that lists all students that have a score under 80 (strict) and no last_meeting or more than 1 month.
Requirements:
- The view need_meeting should return all students name when: * They score are under (strict) to 80 * AND no last_meeting date OR more than a month
bob@dylan:~$ cat 11-init.sql
-- Initial
DROP TABLE IF EXISTS students;
CREATE TABLE IF NOT EXISTS students (
name VARCHAR(255) NOT NULL,
score INT default 0,
last_meeting DATE NULL
);
INSERT INTO students (name, score) VALUES ("Bob", 80);
INSERT INTO students (name, score) VALUES ("Sylvia", 120);
INSERT INTO students (name, score) VALUES ("Jean", 60);
INSERT INTO students (name, score) VALUES ("Steeve", 50);
INSERT INTO students (name, score) VALUES ("Camilia", 80);
INSERT INTO students (name, score) VALUES ("Alexa", 130);
bob@dylan:~$ cat 11-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 11-need_meeting.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 11-main.sql
-- Test view
SELECT * FROM need_meeting;
SELECT "--";
UPDATE students SET score = 40 WHERE name = 'Bob';
SELECT * FROM need_meeting;
SELECT "--";
UPDATE students SET score = 80 WHERE name = 'Steeve';
SELECT * FROM need_meeting;
SELECT "--";
UPDATE students SET last_meeting = CURDATE() WHERE name = 'Jean';
SELECT * FROM need_meeting;
SELECT "--";
UPDATE students SET last_meeting = ADDDATE(CURDATE(), INTERVAL -2 MONTH) WHERE name = 'Jean';
SELECT * FROM need_meeting;
SELECT "--";
SHOW CREATE TABLE need_meeting;
SELECT "--";
SHOW CREATE TABLE students;
bob@dylan:~$
bob@dylan:~$ cat 11-main.sql | mysql -uroot -p holberton
Enter password:
name
Jean
Steeve
--
--
name
Bob
Jean
Steeve
--
--
name
Bob
Jean
--
--
name
Bob
--
--
name
Bob
Jean
--
--
View Create View character_set_client collation_connection
XXXXXX<yes, here it will display the View SQL statement :-) >XXXXXX
--
--
Table Create Table
students CREATE TABLE `students` (\n `name` varchar(255) NOT NULL,\n `score` int(11) DEFAULT '0',\n `last_meeting` date DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1
bob@dylan:~$
# Create task files and set execute permission.
touch 11-need_meeting.sql
chmod +x 11-need_meeting.sql
# Tests
touch 11-init.sql
chmod +x 11-init.sql
# Lint
pycodestyle 11-need_meeting.sql
mypy 11-need_meeting.sqlWrite a SQL script that creates a stored procedure ComputeAverageWeightedScoreForUser that computes and store the average weighted score for a student.
Requirements:
- Procedure ComputeAverageScoreForUser is taking 1 input: * user_id, a users.id value (you can assume user_id is linked to an existing users)
Tips:
- Calculate-Weighted-Average
bob@dylan:~$ cat 100-init.sql
-- Initial
DROP TABLE IF EXISTS corrections;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
CREATE TABLE IF NOT EXISTS users (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
average_score float default 0,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS projects (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
weight int default 1,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS corrections (
user_id int not null,
project_id int not null,
score float default 0,
KEY `user_id` (`user_id`),
KEY `project_id` (`project_id`),
CONSTRAINT fk_user_id FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT fk_project_id FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
);
INSERT INTO users (name) VALUES ("Bob");
SET @user_bob = LAST_INSERT_ID();
INSERT INTO users (name) VALUES ("Jeanne");
SET @user_jeanne = LAST_INSERT_ID();
INSERT INTO projects (name, weight) VALUES ("C is fun", 1);
SET @project_c = LAST_INSERT_ID();
INSERT INTO projects (name, weight) VALUES ("Python is cool", 2);
SET @project_py = LAST_INSERT_ID();
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_c, 80);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_py, 96);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_c, 91);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_py, 73);
bob@dylan:~$
bob@dylan:~$ cat 100-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 100-average_weighted_score.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 100-main.sql
-- Show and compute average weighted score
SELECT * FROM users;
SELECT * FROM projects;
SELECT * FROM corrections;
CALL ComputeAverageWeightedScoreForUser((SELECT id FROM users WHERE name = "Jeanne"));
SELECT "--";
SELECT * FROM users;
bob@dylan:~$
bob@dylan:~$ cat 100-main.sql | mysql -uroot -p holberton
Enter password:
id name average_score
1 Bob 0
2 Jeanne 82
id name weight
1 C is fun 1
2 Python is cool 2
user_id project_id score
1 1 80
1 2 96
2 1 91
2 2 73
--
--
id name average_score
1 Bob 0
2 Jeanne 79
bob@dylan:~$
# Create task files and set execute permission.
touch 100-average_weighted_score.sql
chmod +x 100-average_weighted_score.sql
cat 100-main.sql | mysql -uroot -p holberton
# Tests
touch 100-init.sql
chmod +x 100-init.sqlWrite a SQL script that creates a stored procedure ComputeAverageWeightedScoreForUsers that computes and store the average weighted score for all students.
Requirements:
- Procedure ComputeAverageWeightedScoreForUsers is not taking any input.
Tips:
- Calculate-Weighted-Average
bob@dylan:~$ cat 101-init.sql
-- Initial
DROP TABLE IF EXISTS corrections;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
CREATE TABLE IF NOT EXISTS users (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
average_score float default 0,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS projects (
id int not null AUTO_INCREMENT,
name varchar(255) not null,
weight int default 1,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS corrections (
user_id int not null,
project_id int not null,
score float default 0,
KEY `user_id` (`user_id`),
KEY `project_id` (`project_id`),
CONSTRAINT fk_user_id FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT fk_project_id FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
);
INSERT INTO users (name) VALUES ("Bob");
SET @user_bob = LAST_INSERT_ID();
INSERT INTO users (name) VALUES ("Jeanne");
SET @user_jeanne = LAST_INSERT_ID();
INSERT INTO projects (name, weight) VALUES ("C is fun", 1);
SET @project_c = LAST_INSERT_ID();
INSERT INTO projects (name, weight) VALUES ("Python is cool", 2);
SET @project_py = LAST_INSERT_ID();
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_c, 80);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_bob, @project_py, 96);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_c, 91);
INSERT INTO corrections (user_id, project_id, score) VALUES (@user_jeanne, @project_py, 73);
bob@dylan:~$
bob@dylan:~$ cat 101-init.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 101-average_weighted_score.sql | mysql -uroot -p holberton
Enter password:
bob@dylan:~$
bob@dylan:~$ cat 101-main.sql
-- Show and compute average weighted score
SELECT * FROM users;
SELECT * FROM projects;
SELECT * FROM corrections;
CALL ComputeAverageWeightedScoreForUsers();
SELECT "--";
SELECT * FROM users;
bob@dylan:~$
bob@dylan:~$ cat 101-main.sql | mysql -uroot -p holberton
Enter password:
id name average_score
1 Bob 0
2 Jeanne 0
id name weight
1 C is fun 1
2 Python is cool 2
user_id project_id score
1 1 80
1 2 96
2 1 91
2 2 73
--
--
id name average_score
1 Bob 90.6667
2 Jeanne 79
bob@dylan:~$
# Create task files and set execute permission.
touch 101-average_weighted_score.sql
chmod +x 101-average_weighted_score.sql
# Tests
touch 101-init.sql
chmod +x 101-init.sql