Practice: databases and setup

Optional

45 min · Beginner

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Most early MySQL trouble comes from a handful of habits: losing track of the current database, misreading a table's structure, and scripts that work only once. Each has a quick check and a reliable fix.

Mistake: not knowing which database you are in

Table names in a statement refer to the current database. Right after mysql -u root -p there is none, so SELECT ... FROM customers fails with error 1046, No database selected.

Example 1 · No database selected, and how to check

SQL

SELECT DATABASE();SELECT name FROM customers WHERE customer_id = 1;

Sample output

+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
ERROR 1046 (3D000): No database selected

What this code does

This is what you see right after connecting with mysql -u root -p: DATABASE() is NULL, so MySQL does not know where customers is.
Run USE practice; and the same query returns Asha Rao. It is a sample because it needs a fresh connection with no database chosen.

With the wrong database selected, you may get "table doesn't exist" (error 1146) or, worse, a table of the same name from another database.

SELECT DATABASE(); answers "where am I?": NULL means no database is selected. Fix it with USE practice;, or connect straight into a database with mysql -u root -p practice. In Workbench, the current database is the one shown in bold in the Navigator.

Find the bug

Find the failing line

Each script has one line that fails. Find it.

You just connected with mysql -u root -p.

Round 1 of 2

Reading DESCRIBE and SHOW CREATE TABLE

DESCRIBE table; gives a quick grid of columns: name, type, whether NULL is allowed, the key, the default and extras.

Example 2 · Two views of order_items

SQL

DESCRIBE order_items;SHOW CREATE TABLE order_items;

Output

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| order_id   | int           | NO   | PRI | NULL    |       |
| product_id | int           | NO   | PRI | NULL    |       |
| quantity   | int           | NO   |     | NULL    |       |
| unit_price | decimal(10,2) | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order_items | CREATE TABLE `order_items` (
  `order_id` int NOT NULL,
  `product_id` int NOT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`order_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`),
  CONSTRAINT `order_items_chk_1` CHECK ((`quantity` > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

What this code does

In DESCRIBE, both order_id and product_id say PRI: together they are the primary key, so the same product can appear only once per order.
SHOW CREATE TABLE shows what DESCRIBE cannot: the two foreign keys (named order_items_ibfk_1 and _2) with the tables they point to, the CHECK rule quantity > 0, and the engine and character set. The long line is normal; scroll sideways to read it.

SHOW CREATE TABLE table; prints the full CREATE TABLE statement that would build the table again, including every rule: primary key, unique keys, foreign keys and CHECK rules.

In DESCRIBE, the Key column says PRI for a primary key column (two PRI rows mean the key is made of both columns together), UNI for a unique column and MUL for a column that may repeat but is indexed, usually a foreign key. Use DESCRIBE for a glance and SHOW CREATE TABLE when you need the exact rules.

Mistake: a setup script that works only once

A script with plain CREATE DATABASE or CREATE USER works the first time and fails on every later run with "already exists" errors (1007 for a database, 1050 for a table, 1396 for a user).

Example 3 · A script that fails on the second run, and the fix

SQL

DROP DATABASE IF EXISTS exam_cell;CREATE DATABASE exam_cell;CREATE DATABASE IF NOT EXISTS exam_cell;SHOW CREATE DATABASE exam_cell;CREATE DATABASE exam_cell;

Output

+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                                                                     |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| exam_cell | CREATE DATABASE `exam_cell` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+

ERROR 1007 (HY000): Can't create database 'exam_cell'; database exists

What this code does

The first CREATE builds the database. The IF NOT EXISTS line finds it already there and quietly does nothing, so SHOW CREATE DATABASE still runs.
The last line is the "works only once" mistake: a plain CREATE on an existing name stops the script with error 1007.

The client stops at the first error, so the rest of the script never runs.

Make setup scripts re-runnable: start with DROP ... IF EXISTS when the old copy may be thrown away (practice data), or use CREATE ... IF NOT EXISTS when it must be kept. The practice database script works this way, which is why it doubles as a reset.

Fix the error

Make the script re-runnable

Read the real error, then pick the line that fixes it.

The script is run a second time.

CREATE DATABASE exam_cell;

USE exam_cell;

Error

ERROR 1007 (HY000): Can't create database 'exam_cell'; database exists

DROP really deletes. Use DROP ... IF EXISTS only in scripts for practice or test data, never on a database that holds real data.

DBMS ideas in one place

Apps keep data in a DBMS instead of files because a DBMS gives safe access for many users at once, all-or-nothing transactions that survive crashes, fast search with indexes, and rules and permissions that keep data correct and private.

Data lives in tables of rows and typed columns, tied together by primary and foreign keys. You talk to the DBMS in SQL: DDL for structure, DML for rows, SELECT for reading, TCL for transactions and DCL for permissions. An application connects with its own least-privilege user, never root.

Module quiz

Questions from the whole module. Your first answer is your score.

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

Show the structure of the customers table.

Expected output

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| customer_id | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(60)  | NO   |     | NULL    |                |
| email       | varchar(100) | NO   | UNI | NULL    |                |
| phone       | varchar(15)  | YES  |     | NULL    |                |
| city        | varchar(40)  | NO   |     | NULL    |                |
| joined_on   | date         | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Exercise 2· Easy

A hostel room costs Rs 4500 a month for 10 months, plus a one-time deposit of Rs 5000. Show the total as total_fee and the label Room 101 (built with CONCAT from 'Room ' and 101) as room.

Expected output

+----------+-----------+
| room     | total_fee |
+----------+-----------+
| Room 101 |     50000 |
+----------+-----------+

Exercise 3· Medium

Write a re-runnable script that creates the database exam_cell with utf8mb4 and the utf8mb4_0900_ai_ci collation, switches to it, and shows the current database as current_db.

Expected output

+------------+
| current_db |
+------------+
| exam_cell  |
+------------+

Exercise 4· Medium

Show the full definition of the departments table. From it, name the column that must be unique.

Expected output

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                       |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| departments | CREATE TABLE `departments` (
  `dept_id` int NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(40) NOT NULL,
  `location` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_id`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Exercise 5· Medium

Show the structure of enrollments, then write which columns make up its primary key and which column may be NULL.

Expected output

+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| student_id | int     | NO   | PRI | NULL    |       |
| course_id  | int     | NO   | PRI | NULL    |       |
| marks      | tinyint | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Exercise 6· Medium

This statement fails with ERROR 1146 (42S02): Table 'practice.product' doesn't exist. Fix it so it shows the structure of the products table.

SQL

DESCRIBE product;

Expected output

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| product_id  | int           | NO   | PRI | NULL    | auto_increment |
| name        | varchar(80)   | NO   |     | NULL    |                |
| category_id | int           | YES  | MUL | NULL    |                |
| price       | decimal(10,2) | NO   |     | NULL    |                |
| stock       | int           | NO   |     | 0       |                |
+-------------+---------------+------+-----+---------+----------------+

Exercise 7· Hard

Create a re-runnable read-only user 'exam_viewer'@'localhost' with password Exam#View2025 for the exam_cell database, and show its grants.

Expected output

+------------------------------------------------------------+
| Grants for exam_viewer@localhost                           |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `exam_viewer`@`localhost`            |
| GRANT SELECT ON `exam_cell`.* TO `exam_viewer`@`localhost` |
+------------------------------------------------------------+

Exercise 8· Hard

Read this terminal session and write what each numbered line prints: the value shown, Bye, or the error number.

Text

1mysql -u root -p2(1) mysql> SELECT DATABASE();3(2) mysql> SELECT name FROM courses WHERE course_id = 2;4(3) mysql> USE practice;5(4) mysql> SELECT name FROM courses WHERE course_id = 2;6(5) mysql> SELECT title FROM courses WHERE course_id = 2;7(6) mysql> exit

Expected output

(1) NULL (2) ERROR 1046 (3) Database changed (4) ERROR 1054 (5) Database Systems (6) Bye

Interview questions

Questions interviewers ask on this topic.

What's next

You finished the preview of Databases & MySQL Setup

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Finish up

Mark complete & continue

Next: CREATE TABLE and DROP TABLE

+------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | order_id | int | NO | PRI | NULL | | | product_id | int | NO | PRI | NULL | | | quantity | int | NO | | NULL | | | unit_price | decimal(10,2) | NO | | NULL | | +------------+---------------+------+-----+---------+-------+ +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | order_items | CREATE TABLE `order_items` ( `order_id` int NOT NULL, `product_id` int NOT NULL, `quantity` int NOT NULL, `unit_price` decimal(10,2) NOT NULL, PRIMARY KEY (`order_id`,`product_id`), KEY `product_id` (`product_id`), CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`), CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`), CONSTRAINT `order_items_chk_1` CHECK ((`quantity` > 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | exam_cell | CREATE DATABASE `exam_cell` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ ERROR 1007 (HY000): Can't create database 'exam_cell'; database exists

DBMS ideas in one place

1+------------------------------------------------------------+2| Grants for exam_viewer@localhost |3+------------------------------------------------------------+4| GRANT USAGE ON *.* TO `exam_viewer`@`localhost` |5| GRANT SELECT ON `exam_cell`.* TO `exam_viewer`@`localhost` |6+------------------------------------------------------------+

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | departments | CREATE TABLE `departments` ( `dept_id` int NOT NULL AUTO_INCREMENT, `dept_name` varchar(40) NOT NULL, `location` varchar(40) NOT NULL, PRIMARY KEY (`dept_id`), UNIQUE KEY `dept_name` (`dept_name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | student_id | int | NO | PRI | NULL | | | course_id | int | NO | PRI | NULL | | | marks | tinyint | YES | | NULL | | +------------+---------+------+-----+---------+-------+

+-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | product_id | int | NO | PRI | NULL | auto_increment | | name | varchar(80) | NO | | NULL | | | category_id | int | YES | MUL | NULL | | | price | decimal(10,2) | NO | | NULL | | | stock | int | NO | | 0 | | +-------------+---------------+------+-----+---------+----------------+

+------------------------------------------------------------+ | Grants for exam_viewer@localhost | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO `exam_viewer`@`localhost` | | GRANT SELECT ON `exam_cell`.* TO `exam_viewer`@`localhost` | +------------------------------------------------------------+

1mysql -u root -p2(1) mysql> SELECT DATABASE();3(2) mysql> SELECT name FROM courses WHERE course_id = 2;4(3) mysql> USE practice;5(4) mysql> SELECT name FROM courses WHERE course_id = 2;6(5) mysql> SELECT title FROM courses WHERE course_id = 2;7(6) mysql> exit

Mistake: not knowing which database you are in

What this code does

Reading DESCRIBE and SHOW CREATE TABLE

What this code does

Mistake: a setup script that works only once

What this code does

DBMS ideas in one place

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

You finished the preview of Databases & MySQL Setup

Mistake: not knowing which database you are in

What this code does

Reading DESCRIBE and SHOW CREATE TABLE

What this code does

Mistake: a setup script that works only once

What this code does

DBMS ideas in one place

You just ran mysql -u root -p. What does SELECT DATABASE(); return?

Which command shows the foreign keys and CHECK rules of a table?

In DESCRIBE enrollments, both student_id and course_id show PRI. What does that mean?

Which of these is a command of the mysql client itself, not an SQL statement sent to the server?

What does this print?

Using the practice data, what does this return?

Using the practice data, what does this return?

This script worked yesterday. Today it stops at line 1. Why?

Which line has the error?

What does this output tell you about the user?

A railway app saves a booking with two steps: mark the seat taken, then add the passenger. Which DBMS feature makes sure a crash between them leaves no half-booking?

Which order of commands loads the practice data correctly in the terminal client?

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Q1You are given access to an unfamiliar MySQL database. What do you run first?

Q2What is the difference between DESCRIBE and SHOW CREATE TABLE?

Q3What does MUL mean in the Key column of DESCRIBE?

Q4Why should setup scripts be re-runnable?

Q5Which is worse: error 1146 or a query that runs against the wrong database? Why?

Q6Name four things a DBMS does that a file does not.

You finished the preview of Databases & MySQL Setup

You just ran mysql -u root -p. What does SELECT DATABASE(); return?

Which command shows the foreign keys and CHECK rules of a table?

In DESCRIBE enrollments, both student_id and course_id show PRI. What does that mean?

Which of these is a command of the mysql client itself, not an SQL statement sent to the server?

What does this print?

Using the practice data, what does this return?

Using the practice data, what does this return?

This script worked yesterday. Today it stops at line 1. Why?

Which line has the error?

What does this output tell you about the user?

A railway app saves a booking with two steps: mark the seat taken, then add the passenger. Which DBMS feature makes sure a crash between them leaves no half-booking?

Which order of commands loads the practice data correctly in the terminal client?

Q1You are given access to an unfamiliar MySQL database. What do you run first?

Q2What is the difference between DESCRIBE and SHOW CREATE TABLE?

Q3What does MUL mean in the Key column of DESCRIBE?

Q4Why should setup scripts be re-runnable?

Q5Which is worse: error 1146 or a query that runs against the wrong database? Why?

Q6Name four things a DBMS does that a file does not.

You just ran `mysql -u root -p`. What does `SELECT DATABASE();` return?

In `DESCRIBE enrollments`, both `student_id` and `course_id` show `PRI`. What does that mean?

You just ran `mysql -u root -p`. What does `SELECT DATABASE();` return?

In `DESCRIBE enrollments`, both `student_id` and `course_id` show `PRI`. What does that mean?