Basic SQL commands that every programmer must know

Preamble

​​

SQL or Structured Query Language (Structured Query Language) is intended for data management in a relational database system (RDBMS). In this article, you will learn about frequently used SQL commands that every programmer should be familiar with. This material is ideal for those who want to refresh their knowledge of SQL before a job interview. To do this, parse the examples in the article and remember what passed on the database pairs.

Please note that in some database systems you need to specify a semicolon at the end of each operator. The semicolon is the standard pointer to the end of each SQL statement. The examples use MySQL, so a semicolon is required.

Configuring the database for the examples

Create a database to demonstrate how the commands work. To work, you will need to download two files: DLL.sql and InsertStatements.sql. After that open the terminal and log in to the MySQL console with the following command (the article assumes that MySQL is already installed in the system):

mysql -u root -p

Then enter the password.

Execute the following command. Name the database “university”:

CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;

Teams for working with databases

1. Browse available databases

SHOW DATABASES;

2. Creating a new database

CREATE DATABASE;

3. Selecting the database to use

USE <database_name>;

4. Import SQL commands from a .sql file

SOURCE <path_of_.sql_file>;

5. Deleting the database

DROP DATABASE <database_name>;

Working with tables

6. Viewing the tables available in the database

SHOW TABLES;

7. Creating a new table

CREATE TABLE <table_name1> (
<col_name1> <col_type1>,
<col_name2> <col_type2>,
<col_name3> <col_type3>
PRIMARY KEY (<col_name1>),
FOREIGN KEY (<col_name2>) REFERENCES <table_name2>(<col_name2>)
);

Integrity limitations when using CREATE TABLE

It may be necessary to create restrictions for certain columns in the table. When you create a table, you can set the following restrictions:

• a cell in the table cannot be set to NULL;
• the primary key is PRIMARY KEY (col_name1, col_name2, …);
• external key – FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

It is possible to specify more than one primary key. In this case, a composite primary key is obtained.

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 different information (a type of values, whether it is a key or not) about the columns in the table with the following command:

DESCRIBE <table_name>;

9. Adding data to the table

INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, ...)
VALUES (<value1>, <value2>, <value3>, ...);

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

INSERT INTO <table_name>.
VALUES (<value1>, <value2>, <value3>, ...);

10. Update table data

UPDATE <table_name>
SET <col_name1> = <value1>, <col_name2> = <value2>,...
WHERE <condition>;

11. Deletion of all data from the table

DELETE FROM <table_name>;

12. Deleting a table

DROP TABLE <table_name>;

SQL Commands for creating requests

13. SELECT

SELECT is used to obtain data from a certain table:

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>;

The following command can be used to output all data from the table:

SELECT * FROM <table_name>;

14. SELECT DISTINCT

The columns of the table may contain repetitive data. Use SELECT DISTINCT to obtain only non-repeating data.

SELECT DISTINCT <col_name1>, <col_name2>, ...
FROM <table_name>;

15. WHERE

You can use the WHERE keyword in SELECT to specify conditions in the query:

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>
WHERE <condition>;

You can set the following conditions in your query:

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

Example:
Try to execute the following commands. Pay attention to the conditions defined in WHERE:

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

16. GROUP BY

GROUP BY operator is often used with aggregate functions such as COUNT, MAX, MIN, SUM, and AVG to group output values.

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>
GROUP BY <col_namex>;

Example:
Print the number of courses for each faculty:

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

17. HAVING

The keyword HAVING was added to SQL because WHERE cannot be used to work with aggregate functions.

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>, <col_name2>, ...
GROUP BY <column_namex>
HAVING <condition>

Example: Print the list of faculties which have 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 BY is used to sort the query results in descending or ascending order. ORDER BY will sort in ascending order if no ASC or DESC sorting method is specified.

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>
ORDER BY <col_name1>, <col_name2>, ... ASC|DESC;

Example: Let’s display the list of courses on increasing and decreasing the number of credits:

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

19. BETWEEN

BETWEEN is used to select data values from a certain range. Numerical and text values as well as dates can be used.

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>
WHERE <col_namex> BETWEEN <value1> AND <value2>;

Example: Print a list of 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 LIKE operator is used in WHERE to specify 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 <col_name1>, <col_name2>, ...
FROM <table_name>
WHERE <col_namex> LIKE <pattern>;

Example: Let’s display the list of courses whose name contains “to” and the list of courses whose name starts with “CS-“:

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

21. IN

With IN you can specify several values for the WHERE operator:

SELECT <col_name1>, <col_name2>, ...
FROM <table_name>
WHERE <col_namen> IN (<value1>, <value2>, ...);

Example: Print the list of students from Comp. Sci., Physics, and Elec. Eng..:

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

22. JOIN
JOIN is used to connect two or more tables using common attributes within them. The image below shows the different ways to combine in SQL. Notice the difference between the left external merge and right external merge:

SELECT <col_name1>, <col_name2>, ...
FROM <table_name1>
JOIN <table_name2>
ON <table_name1.col_namex> = <table2.col_namex>;

Example 1: A list of all courses and relevant information about the faculties will be displayed:

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

Example 2: Here is 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 list all courses 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

The view is a virtual SQL table created as a result of an expression execution. It contains rows and columns and is very similar to a regular SQL table. The view always shows the latest information from the database.

Create
CREATE VIEW <view_name> AS
SELECT <col_name1>, <col_name2>, ...
FROM <table_name>
WHERE <condition>;
Delete
DROP VIEW <view_name>;

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

24. Aggregate functions

These functions are used to obtain an aggregate result related to the data in question. The commonly used aggregate functions are given below:

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

25. Nested subqueries

Embedded subqueries are SQL queries that include SELECT, FROM, and WHERE expressions embedded in another query.

An example is: We will find the courses that were taught in autumn 2009 and spring 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
);

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.