FriconiX
Free collection of beautiful vector icons for your web pages.

Lesson 5.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:

-- 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';

Exercice

Let's consider the following table creation:

CREATE TABLE products (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(100),
  price FLOAT
);
  1. Write the queries for inserting the following rows:
Nom Prix
smartphone 399€
laptop 699€
keyboard 29.99€
mouse 19.90€
printer 1650€
modem 99€
  1. Write the query to correct the price of the printer (165 € instead of 1650 €).
  2. Write the query to delete the modem.
  3. Write the query allowing to select all the articles whose price is lower than 200 €.

See also


Last update : 02/04/2020