Ravindra BagaleCourses & study guides मराठी Track your progress

How to Create a Database and Table in MariaDB

By , trainer LinkedIn Last updated:

Every app you use, Instagram included, keeps its users, posts and messages in a database. In this first SQL chapter we install MariaDB on Amazon Linux, log in to it, and build a small Instagram-style database ourselves: we create a database and a users table, choose a data type for each column, add rows with INSERT and read them back with SELECT.

What you'll learn in this class

  • Install and start MariaDB on Amazon Linux, log in with sudo mysql, and set the root password with ALTER USER
  • How the MariaDB prompt works: semicolons, multi-line queries, ->, Ctrl+C, and the socket error
  • Database, table, row and column, and SHOW DATABASES, CREATE DATABASE, USE
  • Data types: INT, VARCHAR, DATE, TIME, FLOAT, BOOLEAN, and why a phone number is VARCHAR
  • CREATE TABLE users, SHOW TABLES and DESC
  • CRUD: INSERT, SELECT, UPDATE, DELETE
  • INSERT with all columns or named columns, NULL, the two common errors, and many rows at once
  • SELECT * versus named columns

1. Install, start and log in to MariaDB

Why: a database server is a program like Apache or Nginx. It must be installed and running before you can store anything.

What: on Amazon Linux the package is mariadb105-server and the service is mariadb. MariaDB is a drop-in replacement for MySQL, so the same SQL and the same mysql command work.

How:

  1. Connect to your Amazon Linux EC2 instance with SSH.
  2. sudo yum install mariadb105-server -y installs the server.
  3. sudo service mariadb start starts it.
  4. sudo service mariadb status should say active (running).
  5. sudo mysql logs you in as the database's root user. The first time, no password is needed because you used sudo.
  6. The prompt changes to MariaDB [(none)]>. You are now talking to the database, not to Linux.

  7. Set a password for root (keep the quotes exactly like this):

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR_STRONG_PASSWORD';

  8. Type exit; to leave.

  9. From now on, log in with mysql -u root -p (or sudo mariadb -u root -p). -u is the user, and -p asks for the password.
  10. Type the password and press Enter. Nothing appears on the screen while you type; that is normal.

After the password is set, plain sudo mysql answers ERROR 1045 ... Access denied: use -u root -p instead.

MariaDB on Amazon Linux: install, start, firstlogin $ sudo yum install mariadb105-server -y 1. Install the MariaDB server. $ sudo service mariadb start 2. Start the database service. $ sudo mysql 3. First login: root, no password yet. MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY'YOUR_STRONG_PASSWORD'; 4. Set a password for root. MariaDB> exit; 5. Leave the MariaDB shell. $ mysql -u root -p 6. From now on: user root, ask for the password.

Figure 1. Animation: install, start, first login with sudo mysql, ALTER USER to set the root password, exit, then mysql -u root -p from then on.

Classroom line

Whenever an error mentions .sock, remember: your database service isn't running.

If you see Can't connect to local server through socket '/var/lib/mysql/mysql.sock', the MariaDB service is stopped. Run sudo service mariadb start and log in again.

2. Talking to MariaDB: semicolons and the arrow prompt

What:

  1. Every SQL statement ends with a semicolon ;. MariaDB runs it only when it sees the ;.
  2. If you press Enter without ;, the prompt changes to ->. MariaDB is waiting for the rest of the statement.
  3. One statement may go over many lines. A keyword itself (DATABASE, CREATE) is never split across two lines.
  4. Ctrl+C cancels the statement you are typing. Ctrl+L clears the screen.
  5. SQL keywords are not case-sensitive: show databases; and SHOW DATABASES; are the same. We write keywords in capitals so they stand out.
  6. Names are different: on Linux, database and table names are case-sensitive by default, so users and Users are two different tables. Pick lowercase names and stick to them.

Classroom line

(Forgetting the semicolon) It happens in a hurry, when you're not used to it yet.

How:

  1. Type SHOW DATABASES and press Enter. You get ->.
  2. Type ; and press Enter. The list appears.
  3. Type SHOW DAT and press Ctrl+C. The half-typed statement is cancelled.
A query runs only when it reachesthe semicolon MariaDB [(none)]> SHOW DATABASES -> ; +--------------------+ | information_schema | | ... | The arrow prompt MariaDB is waiting for the rest. Type ; and press Enter. Changed your mind? Ctrl+C cancels the query. Ctrl+L clears the screen. One query may span many lines; a keyworditself is never split across lines.

Figure 2. A statement runs at the semicolon. The arrow prompt means MariaDB is waiting for the rest; Ctrl+C cancels, Ctrl+L clears the screen.

3. Database, table, row and column

Why: an app keeps different kinds of data: users, posts, reels, chats. Each kind gets its own table, and all the tables of one app live in one database.

Classroom line

What data does Instagram have? Users, reels, posts, chats...

What:

  1. A database is an organised collection of data. One MariaDB server can hold many databases.
  2. A table stores one kind of data, like a spreadsheet.
  3. A column (field) is one piece of information, such as name or city.
  4. A row (record) is one entry, such as one user.
  5. In an interview, describe a database as a container of tables that the database server manages for you. On disk, MariaDB keeps each database as a folder under /var/lib/mysql, but you never edit those files directly; you always work with the data through SQL.

Classroom line

If an interview asks what a database is, don't answer "just a folder".

Database server, database, tables, rows andcolumns MariaDB server instagram database users posts reels chats tables inside the database users one table id name city age 1 ravi solapur 30 2 siya pune 27 3 maya mumbai 25 Column = one field (name). Row = one record (one user). A database is an organised collection of data, stored intables.

Figure 3. A MariaDB server holds databases, a database holds tables, and a table is made of columns (fields) and rows (records).

4. SHOW DATABASES, CREATE DATABASE and USE

Why: An app keeps its tables inside a database of its own. You must create it and select it before any table can exist.

How:

  1. SHOW DATABASES; lists the databases. information_schema, mysql and performance_schema are MariaDB's own; don't change them.
  2. CREATE DATABASE instagram; creates a new database.
  3. Run CREATE DATABASE instagram; again: ERROR 1007 ... database exists. Choose another name, or use the one you have.
  4. SHOW DATABASES; again: instagram is in the list.
  5. USE instagram; answers Database changed, and the prompt changes from MariaDB [(none)]> to MariaDB [instagram]>. Every table you create now goes into instagram.

Classroom line

To create it's CREATE DATABASE, but to see them it's SHOW DATABASES.

5. Data types: what each column may hold

Why: for every column you tell the database what kind of value it holds. Then it can store the value correctly and refuse wrong values.

What:

  1. INT: whole numbers, such as an id or an age. The largest INT is 2,147,483,647.
  2. VARCHAR(n): text of up to n characters: letters, digits, symbols, even emoji. Choose a sensible n, for example VARCHAR(100) for a name.
  3. DATE: a date, written 'YYYY-MM-DD'.
  4. TIME: a time of day, written 'HH:MM:SS'.
  5. FLOAT: numbers with decimals, such as a weight. For money, use DECIMAL, which stores exact values.
  6. BOOLEAN: true or false (MariaDB stores it as 1 or 0).

Classroom line

For the phone number column, will you use integer or varchar?

A phone number is VARCHAR:

  1. A 10-digit mobile number is bigger than 2,147,483,647, so INT refuses it with an Out of range error.
  2. Phone numbers may have +91, spaces or a leading 0, which a number type would lose.
  3. You never add or multiply phone numbers. If you don't do maths on it, store it as text.
Common data types: pick one for every column INT Whole numbers 30, -5, 1000 Max 2,147,483,647 VARCHAR(n) Text up to n characters 'ravi', 'Pune411001' Letters, digits, symbols DATE A date '1998-07-15' YYYY-MM-DD TIME A time of day '14:30:00' HH:MM:SS FLOAT Numbers with decimals 72.5 DECIMAL for exact money BOOLEAN True or false TRUE, FALSE Stored as 1 and 0 Phone number? VARCHAR, not INT. A 10-digit number is too big for INT, and phones have +91, spaces or aleading 0. You never do maths on them.

Figure 4. Common data types with examples. A phone number goes in VARCHAR: too big for INT, it may contain +91 or spaces, and you never do maths on it.

6. CREATE TABLE, SHOW TABLES and DESC

Why: A table is the shape your data will have for years. Checking it with DESC right after creating it catches a wrong type before any data goes in.

How:

  1. Make sure the prompt says [instagram] (run USE instagram; if not).

  2. Create the table:

    CREATE TABLE users (id INT, name VARCHAR(100), city VARCHAR(50), age INT);

  3. Query OK, 0 rows affected means the table exists, still empty.

  4. SHOW TABLES; lists the tables in the database: users.
  5. DESC users; (or DESCRIBE users;) shows each column, its type, whether it may be empty (Null) and its default value.

Classroom line

Name the table users, not user. USER is a keyword in SQL.

We call the table users because USER is an SQL keyword, and the built-in mysql database already has a table called user. A plural name avoids that confusion.

MariaDB terminal: use instagram; answers Database changed; create table users( id int , name varchar(50), city varchar(50), age int); answers Query OK, 0 rows affected; show tables; prints a table with the heading Tables_in_instagram and one row, users.

Whiteboard: USE instagram, CREATE TABLE users with four columns, and SHOW TABLES listing users.

In DESC, you may see int(11). The 11 is only a display width from older MySQL versions; it doesn't change what an INT can store.

MariaDB terminal: describe users; prints a table with columns Field, Type, Null, Key, Default and Extra, and four rows: id int(11), name varchar(50), city varchar(50), age int(11), each with Null YES and Default NULL.

Whiteboard: DESC users: each column with its type, Null, Key and Default.

Common mistakes that give ERROR 1064 (a syntax error):

  1. A missing comma between two columns.
  2. VARCHAR without a size.
  3. A missing ) at the end.
Reading a CREATE TABLE statement CREATE TABLE users ( Table name: users, not user. id INT, Column name, then its type. name VARCHAR(100), VARCHAR always needs a size. city VARCHAR(50), A comma after every column ... age INT ... except the last one. ); Close the bracket, end with ; Then check it: SHOW TABLES; lists the tables, DESC users; showsevery column and its type.

Figure 5. Reading CREATE TABLE users: column name then type, VARCHAR needs a size, commas between columns, close the bracket and end with a semicolon.

Ravindra Bagale's Tip

Keep table and column names lowercase, with no spaces (users, city, created_at). On Linux, names are case-sensitive, and one consistent style saves you from "table doesn't exist" errors later.

7. CRUD: the four things you do with data

Why: Almost every screen in an app is one of these four actions. Knowing the four names helps you read any app's SQL.

What: almost everything an app does with its rows is one of four actions, together called CRUD:

  1. Create = INSERT: add new rows.
  2. Read = SELECT: read rows. It never changes anything.
  3. Update = UPDATE: change rows that exist, for example UPDATE users SET age = 31 WHERE id = 1;
  4. Delete = DELETE: remove rows, for example DELETE FROM users WHERE id = 3;

CREATE DATABASE, CREATE TABLE and ALTER TABLE are not CRUD. They change the structure; CRUD works on the rows inside the tables. In this chapter we practise INSERT and SELECT.

CRUD: the four things you do with data C Create INSERT add new rows R Read SELECT read rows; changes nothing U Update UPDATE change existing rows D Delete DELETE remove rows Not CRUD: structure commands CREATE DATABASE, CREATE TABLE and ALTERchange the structure. CRUD works on the rows inside.

Figure 6. CRUD: Create = INSERT, Read = SELECT, Update = UPDATE, Delete = DELETE. CREATE TABLE and ALTER are structure commands, not CRUD.

8. INSERT: add rows

Why: An empty table is useless. Every sign-up, post or order in an app ends as one INSERT.

8.1 All columns, in table order

  1. DESC users; shows the column order: id, name, city, age.

  2. Give one value for every column, in that order:

    INSERT INTO users VALUES (1, 'ravi', 'solapur', 30);

  3. Text goes in single quotes ('ravi'); numbers don't (30).

  4. Query OK, 1 row affected means the row is saved.
  5. SELECT * FROM users; shows it.

8.2 Named columns, in any order

  1. List the columns you want to fill, then the values in the same order:

    INSERT INTO users (name, id, city) VALUES ('zoya', 2, 'pune');

  2. The column you left out, age, gets NULL.

  3. This form is safer: months later you won't remember the order the table was created in, and you don't need to.

Classroom line

If you come to insert values four or five months later, how will you know the column order the table was created with?

8.3 NULL

  1. NULL means no value at all. It is not empty text '' and not 0.
  2. INSERT INTO users (name, id) VALUES ('siya', 3); leaves city and age as NULL.

Classroom line

In any column where we don't put a value, NULL will appear.

Classroom line

NULL means nothing at all; the value doesn't exist.

8.4 Two errors you will meet

  1. Wrong number of values: INSERT INTO users VALUES (4, 'amit', 'nashik'); gives three values for four columns: ERROR 1136 ... Column count doesn't match value count at row 1. Give all four values, or name the columns.
  2. Values in the wrong order: INSERT INTO users (name, id, city) VALUES ('zoya', 'pune', 2); puts 'pune' into id: ERROR 1366 ... Incorrect integer value: 'pune' for column ... id. Put the values in the order of your column list.
MariaDB terminal: insert into users values(1,'ravi','solapur',30); Query OK; select * from users; shows one row. insert into users(name,id,city) values('zoya', 2,'pune'); Query OK. insert into users(name,id,city) values('zoya', 'pune',2); answers ERROR 1366 (22007): Incorrect integer value: 'pune' for column instagram.users.id at row 1. select * from users; shows 1 ravi solapur 30 and 2 zoya pune NULL.

Whiteboard: INSERT with all columns, INSERT with named columns (age becomes NULL), and the error when a value goes into the wrong column.

INSERT: all columns in table order, orthe columns you name 1. All columns, table order INSERT INTO users VALUES (1, 'ravi', 'solapur', 30); One value for every column, in the order DESC shows. Text in quotes, numbers without. 2. Named columns, any order INSERT INTO users (name, id, city) VALUES ('zoya', 2, 'pune'); Values follow YOUR column list. Skipped column age becomes NULL. Safer: you need not remember the order. Two errors you will see ERROR 1136: Column count doesn't match valuecount at row 1 ERROR 1366: Incorrect integer value: 'pune'for column ...id Many rows in one INSERT: VALUES (...), (...), (...);gives Query OK, 3 rows affected.

Figure 7. INSERT with all columns in table order, or with named columns in any order (skipped columns become NULL), and the two errors you will meet.

8.5 Many rows in one INSERT

When you fill a new app with test data, typing one INSERT per row is slow. Put several rows in one statement, separated by commas:

INSERT INTO users VALUES (1, 'ravi', 'solapur', 30), (2, 'siya', 'pune', 27), (3, 'maya', 'mumbai', 25);
  1. MariaDB answers Query OK, 3 rows affected.
  2. The table has no rule against duplicates yet, so the same id can appear twice. Keys that prevent this come later.
MariaDB terminal: insert into users values(1,'ravi','solapur',30),(2,'siya','pune',27),(3,'maya','mumbai',25); answers Query OK, 3 rows affected and Records: 3 Duplicates: 0 Warnings: 0.

Whiteboard: One INSERT with three rows: Query OK, 3 rows affected.

9. SELECT: read rows

Why: Data is stored to be read. Every feed, profile and search result is a SELECT, and it never changes anything, so it is safe to practise.

What: SELECT reads rows and shows them. It never changes the data.

Classroom line

Only the SELECT query is a read query. It doesn't change the data.

How:

  1. SELECT * FROM users; shows all columns of every row.
  2. SELECT name FROM users; shows only the name column.
  3. SELECT id, name FROM users; shows two columns.
  4. SELECT city, name FROM users; shows them in the order you asked: city first.
  5. SELECT city FROM users; may show the same city several times, one per row.
  6. SELECT DISTINCT city FROM users; shows each city once. We look at DISTINCT properly in the next SQL chapter.

Classroom line

Everyone thinks star means all records. That's wrong: star means the data of all columns.

SELECT: star means every column, not every row SELECT * FROM users; id | name | city | age 1 | ravi | solapur | 30 2 | zoya | pune | NULL 3 | maya | mumbai | 25 1. Star: every column of every row. SELECT name, city FROM users; name | city ravi | solapur zoya | pune maya | mumbai 2. Named columns: only those columns, still every row. SELECT city, name FROM users; city | name solapur | ravi pune | zoya mumbai | maya 3. Columns come back in the order you ask for. SELECT only reads.

Figure 8. Animation: SELECT * returns every column, SELECT name, city returns only those columns, and the columns come back in the order you ask for.

When you're done practising, type exit; and stop the EC2 instance.

10. The whole flow on one page

  1. sudo yum install mariadb105-server -y, sudo service mariadb start.
  2. sudo mysql, then ALTER USER 'root'@'localhost' IDENTIFIED BY '...';, exit;, then mysql -u root -p.
  3. End every statement with ;. -> means "waiting for the rest"; Ctrl+C cancels. A .sock error means the service is stopped.
  4. SHOW DATABASES;, CREATE DATABASE instagram;, USE instagram;.
  5. Pick a type per column: INT, VARCHAR(n), DATE, TIME, FLOAT, BOOLEAN; phone numbers in VARCHAR.
  6. CREATE TABLE users (...);, SHOW TABLES;, DESC users;.
  7. CRUD = INSERT, SELECT, UPDATE, DELETE.
  8. INSERT INTO users VALUES (...); or INSERT INTO users (cols) VALUES (...);; skipped columns become NULL; many rows with (...), (...).
  9. SELECT * FROM users; = all columns; SELECT name, city FROM users; = chosen columns.

11. Hands-on lab: your first database, table and rows

Why this lab. This lab goes from an empty server to a table with data you can read back, in the order an app needs it: server, database, table, rows, query.

  1. Install and start MariaDB
    • Command: sudo yum install mariadb105-server -y && sudo service mariadb start
    • You should see: Complete!, then Redirecting to /bin/systemctl start mariadb.service.
    • Check: sudo service mariadb status shows active (running).
    • Why: A database server is a program like Apache: installed, then running. If it's stopped, logins fail with an error about mysql.sock.
  2. Log in
    • Command: sudo mysql
    • You should see: The prompt MariaDB [(none)]>.
    • Check: If you already set a root password, use mysql -u root -p instead.
    • Why: (none) in the prompt means no database is selected yet. From here on you type SQL, not Linux commands.
  3. Create and select a database
    • Command: CREATE DATABASE instagram; USE instagram;
    • You should see: Query OK, 1 row affected, then Database changed.
    • Check: The prompt becomes MariaDB [instagram]>.
    • Why: Each statement ends with ;. Without USE, the next CREATE TABLE fails with No database selected.
  4. Create a table
    • Command: CREATE TABLE users (id INT, name VARCHAR(50), city VARCHAR(50), age INT);
    • You should see: Query OK, 0 rows affected
    • Check: DESC users; lists four fields: id int(11), name varchar(50), city varchar(50), age int(11).
    • Why: Each column gets a type, so the database refuses wrong values. VARCHAR(50) holds text up to 50 characters.
  5. Insert three rows
    • Command: INSERT INTO users VALUES (1, 'ravi', 'solapur', 30), (2, 'siya', 'pune', 27), (3, 'maya', 'mumbai', 25);
    • You should see: Query OK, 3 rows affected and Records: 3 Duplicates: 0 Warnings: 0.
    • Check: No error.
    • Why: Values go in table order. Text needs quotes; numbers don't. One INSERT can add many rows.
  6. Insert with named columns
    • Command: INSERT INTO users (name, id, city) VALUES ('zoya', 4, 'pune');
    • You should see: Query OK, 1 row affected
    • Check: No error, even though age was left out.
    • Why: Naming the columns frees you from remembering the table order. A column you skip becomes NULL.
  7. Read everything
    • Command: SELECT * FROM users;
    • You should see: A table with 4 rows, and 4 rows in set. Zoya's age shows NULL.
    • Check: The rows match what you inserted.
    • Why: * means every column, not every row. Reading back is how you prove the inserts worked.
  8. Read only some columns
    • Command: SELECT name, city FROM users;
    • You should see: Two columns, 4 rows.
    • Check: age and id are not shown.
    • Why: Apps ask only for what a screen shows. Fewer columns means less data sent over the network.
  9. Leave
    • Command: exit;
    • You should see: Bye
    • Check: You are back at the Linux prompt. Keep the instagram database for the next SQL chapters, and stop the instance.
    • Why: The data stays on the disk after you leave and after a stop, as long as you don't terminate the instance.

12. Try at home

Try at home

Task 1: set up

  1. Install and start MariaDB, log in with sudo mysql, and set a root password with ALTER USER.
  2. exit; and log in again with mysql -u root -p.
  3. Stop the service with sudo service mariadb stop, try to log in, and read the socket error. Start it again.

Task 2: database and table

  1. Create a database school and USE it.
  2. Create a table students with id INT, name VARCHAR(100), phone VARCHAR(15), marks FLOAT and joined DATE.
  3. Check it with SHOW TABLES; and DESC students;.

Task 3: INSERT and SELECT

  1. Insert one student with all columns, and one with only id and name. Which values became NULL?
  2. Insert three students in one statement.
  3. Make the column-count error and the wrong-order error on purpose, and read both messages.
  4. Run SELECT *, then SELECT name, marks, then SELECT marks, name.

Recap

In short

  1. MariaDB on Amazon Linux: mariadb105-server, service mariadb; first login sudo mysql, then ALTER USER 'root'@'localhost' IDENTIFIED BY '...'; and mysql -u root -p.
  2. Statements end with ;; -> waits for the rest; Ctrl+C cancels; a .sock error means the service is stopped.
  3. Keywords are not case-sensitive; database and table names are case-sensitive on Linux.
  4. A database holds tables; a table has columns (fields) and rows (records).
  5. SHOW DATABASES, CREATE DATABASE, USE, CREATE TABLE, SHOW TABLES, DESC.
  6. Types: INT (max 2,147,483,647; int(11) is only display width), VARCHAR(n), DATE, TIME, FLOAT, BOOLEAN; phone numbers in VARCHAR.
  7. CRUD = INSERT, SELECT, UPDATE, DELETE.
  8. INSERT all columns in order, or named columns in any order; skipped columns are NULL; many rows in one statement.
  9. SELECT * = every column; name the columns you need, in the order you want.

Practice Tasks

About this scenario

Solapur Chaddar House is a home textiles business in Solapur. Vikas Kamble looks after its IT and cloud work. They count money in INR and run their AWS resources in the ap-south-1 Region. In the tasks below you do real jobs for them using what this lesson teaches. All names, account IDs, IP addresses and numbers are made up for practice.

0 / 10

Easy Biggest orders first

Why: Querying the database is daily work after you set up RDS.

What to do: Show order id and amount, biggest amount first.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Easy Filter customers by city

Why: Querying the database is daily work after you set up RDS.

What to do: List the names of customers in Pune.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Easy Log in to MariaDB

Why: The mysql client lets you run SQL on the database server.

What to do: Open the MariaDB client as user root and ask for the password.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Medium Total sales

Why: Querying the database is daily work after you set up RDS.

What to do: Show the total of all order amounts as one number.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Medium Orders with customer names

Why: Querying the database is daily work after you set up RDS.

What to do: Show each customer name with each order amount.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Medium Remove small orders

Why: Querying the database is daily work after you set up RDS.

What to do: Delete orders with amount below 200.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Medium Fix a city

Why: Querying the database is daily work after you set up RDS.

What to do: Customer 5 moved to Rome. Update the record.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Hard Sales per customer

Why: Querying the database is daily work after you set up RDS.

What to do: Show customer_id and total amount per customer, only where total is above 1000.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Hard Customers with no orders

Why: Querying the database is daily work after you set up RDS.

What to do: List names of customers who have never ordered.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Hard Back up a database to a file

Why: mysqldump writes the whole database as SQL. Save it before big changes.

What to do: Dump the database solapurchaddar_wp as user root (ask for password) into solapurchaddar_wp.sql.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

See all practice tasks in the Task Hub →

Revision, comparison and interview cards

Revise this lesson in a few minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens this lesson.

Revision card: Database, table, row, column. Apps need data stored in a clear, searchable shape. A database holds tables. A table has columns (fields) and rows (records). CREATE DATABASE, USE, then CREATE TABLE. Start:
Revision card: Database, table, row, column · Download PNG
Revision card: SQL data types. The wrong type loses data, like a phone number losing its leading 0. INT whole numbers, VARCHAR text, DATE, TIME, FLOAT decimals, BOOLEAN true/false. Pick by how the value is used, not how it looks. Phone is text:
Revision card: SQL data types · Download PNG
Revision card: CREATE TABLE and DESC. Data needs a table with named, typed columns first. CREATE TABLE defines columns. DESC shows the structure. End with ;. Multi-line is fine; -> means continue. Table:
Revision card: CREATE TABLE and DESC · Download PNG
Revision card: CRUD in SQL. Every app does four things with data. Create = INSERT, Read = SELECT, Update = UPDATE, Delete = DELETE. Always use WHERE with UPDATE and DELETE. Insert many:
Revision card: CRUD in SQL · Download PNG
Comparison: INT vs VARCHAR. INT: A whole number you can do maths with. VARCHAR: Text up to a set length.
Comparison: INT vs VARCHAR · Download PNG
Interview card: What is CRUD?. Create, Read, Update, Delete: INSERT, SELECT, UPDATE, DELETE. Every data app is built from these four. Sign-up = INSERT; profile = SELECT; edit = UPDATE; close = DELETE.
Interview card: What is CRUD? · Download PNG
Interview card: Why avoid SELECT * in apps?. It fetches every column, even big or secret ones. Naming columns is faster and safer if the table changes. Better:
Interview card: Why avoid SELECT * in apps? · Download PNG
Interview card: MariaDB says "Can't connect through socket". What does it mean?. The MariaDB server is not running. The client looks for the server's socket file and finds none. Fix:
Interview card: MariaDB says "Can't connect through socket". What does it mean? · Download PNG

More cards: Ravi Sir's Cards.