SQL or S tructured Query Language (structured query language) is designed to manage data in a relational database system (RDBMS). This article will talk about commonly used SQL commands that every programmer should be familiar with. This material is ideal for those looking to brush up on their SQL knowledge before a job interview. To do this, analyze the examples given in the article and remember what you went through in pairs through the databases.
Note that some database systems require a semicolon at the end of each statement. The semicolon is a standard pointer to the end of every statement in SQL. The examples use MySQL, so a semicolon is required.
Database setup for examples
Create a database to demonstrate how teams work. To work, you need to download two files: DLL.sql and InsertStatements.sql . After that, open a terminal and enter the MySQL console using the following command (this article assumes that MySQL is already installed on the system):
mysql -u root -p
Then enter your password.
Run the following command. Let’s call the database “university”:
CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;
Database Commands
- View available databases
SHOW DATABASES;
- Creating a new database
CREATE DATABASE;
- Choosing a database to use
USE <database_name>;
- Importing SQL commands from a .sql file
SOURCE <path_of_.sql_file>;
- Removing the database
DROP DATABASE <database_name>;
Working with tables
- Viewing tables available in the database
SHOW TABLES;
- 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 constraints in use CREATE TABLE
It may be necessary to create constraints for specific columns in the table. When creating a table, you can set the following restrictions:
- a 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).
More than one primary key can be specified. In this case, you get a composite primary key.
Example
Create table “instructor”:
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)
);
- Table information
You can view various information (value type, key or not) about the table columns with the following command:
DESCRIBE <table_name>;
- 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 in the table, you do not need to specify the column names.
INSERT INTO <table_name>
VALUES (<value1>, <value2>, <value3>, …);
- Updating table data
UPDATE <table_name>
SET <col_name1> = <value1>, <col_name2> = <value2>, …
WHERE <condition>;
- Removing all data from the table
DELETE FROM <table_name>;
- Deleting a table
DROP TABLE <table_name>;
Commands for making requests
- SELECT
SELECT used to retrieve data from a specific table:
SELECT <col_name1>, <col_name2>, …
FROM <table_name>;
The following command can display all data from the table:
SELECT * FROM <table_name>;
- SELECT DISTINCT
Table columns can contain duplicate data. Use SELECT DISTINCTto get only non-duplicate data.
SELECT DISTINCT <col_name1>, <col_name2>, …
FROM <table_name>;
- WHERE
You can use the WHEREc keyword SELECTto specify conditions in the request:
SELECT <col_name1>, <col_name2>, …
FROM <table_name>
WHERE <condition>;
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 set 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;
- GROUP BY
The operator GROUP BYoften used with aggregate functions, such as COUNT, MAX, MIN, SUMand AVG, for groups of output values.
SELECT <col_name1>, <col_name2>, …
FROM <table_name>
GROUP BY <col_namex>;
Example
Let’s display the number of courses for each faculty:
SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name;
- HAVING
The keyword HAVINGwas added to SQL because it WHEREcannot be used with aggregate functions.
SELECT <col_name1>, <col_name2>, …
FROM <table_name>
GROUP BY <column_namex>
HAVING <condition>
Example
Let’s list the faculties that have more than one course:
SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name
HAVING COUNT(course_id)>1;
- ORDER BY
ORDER BYused to sort query results in descending or ascending order. ORDER BYwill sort in ascending order if no sorting method is specified ASCor DESC.
SELECT <col_name1>, <col_name2>, …
FROM <table_name>
ORDER BY <col_name1>, <col_name2>, … 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;
- BETWEEN
BETWEENis used to select data values from a specified range. Numeric 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
Here’s a list of instructors whose salary is over 50,000 but less than 100,000:
SELECT * FROM instructor
WHERE salary BETWEEN 50000 AND 100000;
- LIKE
The operator is LIKEused in WHEREto specify a search pattern for a similar value.
PHP developer
AGIMA, Moscow, from 000 160,000
tproger.ru
Vacancies at tproger.ru
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 a list of courses, the name of which contains «to», and a list of courses, the name of which begins with «CS-»:
SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE ‘CS-___’;
- IN
Using, INyou can specify multiple values for the operator WHERE:
SELECT <col_name1>, <col_name2>, …
FROM <table_name>
WHERE <col_namen> IN (<value1>, <value2>, …);
Example
Let’s display a list of students from Comp. Sci. Physics and Elec. Eng .:
SELECT * FROM student
WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);
- JOIN
JOINused to link two or more tables using common attributes within them. The image below shows the different ways to combine in SQL. Notice the difference between a left outer join and a right outer join:
SELECT <col_name1>, <col_name2>, …
FROM <table_name1>
JOIN <table_name2>
ON <table_name1.col_namex> = <table2.col_namex>;
Example 1
We will display a list of all courses and the corresponding information about the faculties:
SELECT * FROM course
JOIN department
ON course.dept_name=department.dept_name;
Example 2
Let’s list all the 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
Let’s list all the 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;
- View
ViewIs a virtual SQL table created by executing an 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 <view_name> AS
SELECT <col_name1>, <col_name2>, …
FROM <table_name>
WHERE <condition>;
Deleting
DROP VIEW <view_name>;
Example
Let’s create viewone consisting of courses with 3 credits:
- Aggregate functions
These functions are used to obtain an aggregate result for the data in question. The following are commonly used aggregate functions:
- COUNT (col_name)- returns the number of rows;
- SUM (col_name)- returns the sum of the values in the given column;
- AVG (col_name)- returns the average value of the given column;
- MIN (col_name)- returns the smallest value of the given column;
- MAX (col_name)- returns the largest value of the given column.
- Nested subqueries
Nested subqueries are SQL queries that include expressions SELECT, FROMand WHEREare nested within another query.
Example
Let’s 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
