Lesson 6.3. SQL queries

SQL: a query language

Each command sent to the database engine is called a query. On this page, we will list the most used queries

Create a new table

The following command creates a new users table by defining each of the fields:

CREATE TABLE users (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  lastname VARCHAR(100),
  firstname VARCHAR(100),
  email VARCHAR(255)
);

Insert a new row

The following query creates a new row in the table, specifying the data contained in this line. Note that the id field is not defines because, this column was set as auto-increment.

INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Lary', 'Page', 'lary.page@google.com');

Update a row

When updating a row, you need of course to specify which line it is, here by its identifier id.

UPDATE `users` SET email = 'lary.page@gmail.com' WHERE id=1;

Delete a row

As for updating, you must specify which row to delete. The next command will delete all rows where the user's lastname is Page:

DELETE FROM `users` WHERE lastname='Page';

Example

The following example:

creates a table named users;
populates the table with 5 rows;
update a row;
delete a row;
select rows.

-- Create table
CREATE TABLE users (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  lastname VARCHAR(100),
  firstname VARCHAR(100),
  email VARCHAR(255)
);

-- Insert rows
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Lary', 'Page', 'lary.page@google.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Sergueï', 'Brin', 'serguei.brin@google.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Elon', 'Musk', 'elon.musk@tesla.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Steve', 'Jobs', 'steve.jobs@apple.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Noël', 'Père', 'santa@clause.com');

-- Update row
UPDATE `users` SET email = 'steve.jobs@paradise.com' WHERE lastname="jobs";

-- Delete row
DELETE FROM `users` WHERE email='santa@clause.com';

-- Select the full table
SELECT * FROM `users`;

-- Select rows sorted by lastname
SELECT * FROM `users` ORDER BY lastname;

-- Select rows for a given last name
SELECT * FROM `users` WHERE lastname = 'Page';

Tester l'exemple en ligne

Exercice

Let's consider the following table creation:

CREATE TABLE products (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(100),
  price FLOAT
);

Write the queries for inserting the following rows:

Nom	Prix
smartphone	399€
laptop	699€
keyboard	29.99€
mouse	19.90€
printer	1650€
modem	99€

Write the query to correct the price of the printer (165 € instead of 1650 €).
Write the query to delete the modem.
Write the query allowing to select all the articles whose price is lower than 200 €.

Faire l'exercice en ligne

Solution

Lesson 6.2 Summary Lesson 6.4