SQL or Structured Query Language (structured query language) is designed to manage data in a relational database system (RDBMS). This article will cover the frequently used SQL commands that every programmer should be familiar with. This material is ideal for those who want to refresh their knowledge about SQL before interviewing for work. To do this, sort out the examples given in the article and remember what went on in pairs through the databases.

Note that on some database systems, a semicolon is required at the end of each statement. The semicolon is the standard pointer to the end of each statement in SQL. The examples use MySQL, so a semicolon is required.

Database setup for examples

Create a database to demonstrate how teams work. After that, open a terminal and log into the MySQL console using the following command (the article assumes that MySQL is already installed in the system):

mysql -u root -p

Then enter the password.

Run the following command. Let’s call the database “university”:

CREATE DATABASE university;
USE university;
SOURCE ;
SOURCE ;

Commands for working with databases

1. View available databases

SHOW DATABASES;

2. Creating a new database

CREATE DATABASE;

3. Choose a database to use.

USE ; 

4. Importing SQL commands from a .sql file

SOURCE ; 

5. Delete Database

DROP DATABASE ; 

Work with tables

6. View the tables available in the database

SHOW TABLES; 

7. Creating a new table

CREATE TABLE  (
  ,
  ,
  
  PRIMARY KEY (),
  FOREIGN KEY () REFERENCES ()
); 

Integrity constraints when using CREATE TABLE

You may need to create constraints for specific columns in a Table. When creating a table, you can set the following restrictions:

• table cell cannot be NULL;
• primary key – PRIMARY KEY (col_name1, col_name2, …);
• foreign key – FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

You can specify more than one primary key. In this case, you will get a composite primary key.

Example

Create an instructor table:

CREATE TABLE instructor (
  ID CHAR(5),
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  salary NUMERIC(8,2),
  PRIMARY KEY (ID),
  FOREIGN KEY (dept_name) REFERENCES department(dept_name)
); 

8. Information about the table

You can view various details (the type of values, whether it is a key or not) about the table columns with the following command:

DESCRIBE ; 

9. Adding data to the table

INSERT INTO  (, , , …)
  VALUES (, , , …); 

When adding data to each column of the table, you do not need to specify column names.

INSERT INTO 
  VALUES (, , , …); 

10. Updating table data

UPDATE 
  SET  = ,  = , ...
  WHERE ; 

11. Delete all data from the table

DELETE FROM ; 

12. Delete table

DROP TABLE ; 

Commands for creating queries

13. SELECT

SELECT used to retrieve data from a specific table:

SELECT , , …
  FROM ; 

The following command can display all data from the table:

SELECT * FROM ; 

14. SELECT DISTINCT

The table columns may contain duplicate data. Use SELECT DISTINCTto get only non-repeating data.

SELECT DISTINCT , , …
  FROM ; 

15. WHERE

You can use the keyword WHEREin SELECTto specify conditions in the query:

SELECT , , …
  FROM 
  WHERE ; 

The following conditions can be specified in the request:

• text comparison;
• comparison of numerical values;
• logical operations AND (and), OR (or) and NOT (negation).

Example

Try the following commands. Pay attention to the conditions specified in WHERE:

SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3; 

16. GROUP BY

The operator GROUP BYoften used with aggregate functions, such as COUNT, MAX, MIN, SUMand AVG, for groups of output values.

SELECT , , …
  FROM 
  GROUP BY ; 

Example

We derive the number of courses for each faculty:

SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name; 

17. HAVING

The keyword HAVINGwas added to SQL because it WHEREcannot be used to work with aggregate functions.

SELECT , , ...
  FROM 
  GROUP BY 
  HAVING 

Example

Let’s display a list of faculties with more than one course:

SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name
  HAVING COUNT(course_id)>1; 

18. ORDER BY

ORDER BYused to sort the query results in descending or ascending order. ORDER BYsorts by ascending if no sorting method is specified ASCor DESC.

SELECT , , …
  FROM 
  ORDER BY , , … ASC|DESC; 

Example

Let’s display a list of courses in ascending and descending order of the number of credits:

SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

BETWEENUsed to Select data values ​​from a specific range. Numeric and text values ​​can be used, as well as dates.

SELECT , , …
  FROM 
  WHERE  BETWEEN  AND ; 

Example

We’ll list the instructors whose salary is more than 50,000, but less than 100,000:

SELECT * FROM instructor
  WHERE salary BETWEEN 50000 AND 100000; 

20. LIKE

The statement is LIKEused WHEREto set a search pattern for a similar value.

There are two free operators that are used in LIKE:

• % (none, one or more characters);
• _ (one character).

SELECT , , …
  FROM 
  WHERE  LIKE ; 

Example

We will display a list of courses whose name contains «to», and a list of courses whose names begin with «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___'; 

21. IN

With the help INyou can specify multiple values ​​for the operator WHERE:

SELECT , , …
  FROM 
  WHERE  IN (, , …); 

Example

We will display a list of students from the directions Comp. Sci., Physics and Elec. Eng .:

SELECT * FROM student
  WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’); 

22. JOIN

JOINused to link two or more tables using common attributes within them. The image below shows the various ways to merge in SQL. Note the difference between the left outer join and the right outer join:

SELECT , , …
  FROM 
  JOIN 
  ON  = ; 

Example 1

We will display a list of all courses and relevant information about the departments:

SELECT * FROM course 
    JOIN department 
    ON course.dept_name=department.dept_name;

Example 2

We will display a list of all required courses and details about them:

SELECT prereq.course_id, title, dept_name, credits, prereq_id
  FROM prereq
  LEFT OUTER JOIN course
  ON prereq.course_id=course.course_id; 

Example 3

We will display a list of all courses, regardless of whether they are required or not:

SELECT course.course_id, title, dept_name, credits, prereq_id
  FROM prereq
  RIGHT OUTER JOIN course
  ON prereq.course_id=course.course_id; 

23. View

ViewIs a virtual SQL table created as a result of the expression. It contains rows and columns and is very similar to a regular SQL table. Viewalways shows the latest information from the database.

Creature

CREATE VIEW  AS
  SELECT , , …
  FROM 
  WHERE ; 

Deletion

DROP VIEW ; 

Example

Let’s create view, consisting of courses with 3 credits:

24. Aggregate functions

These functions are used to obtain a cumulative result relating to the data under consideration. The following are common aggregate functions:

• COUNT (col_name) – returns the number of rows;
• SUM (col_name) – returns the sum of the values ​​in this column;
• AVG (col_name) – returns the average value of this column;
• MIN (col_name) – returns the smallest value of the given column;
• MAX (col_name) – returns the largest value of this column.

25. Nested subqueries

Nested subqueries – a SQL-queries that include expressions SELECT, FROMand WHEREinvested in another request.

Example

Find the courses taught in the fall of 2009 and the spring of 2010:

SELECT DISTINCT course_id
  FROM section
  WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
    SELECT course_id
    FROM section
    WHERE semester = ‘Spring’ AND year= 2010
  ); 

The post Basic SQL commands that every programmer should know appeared first on TECHY360.