Basic SQL commands that every programmer should know
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 <path_of_DLL.sql_file>; SOURCE <path_of_InsertStatements.sql_file>;
Commands for working with databases
1. View available databases
2. Creating a new database
3. Choose a database to use.
4. Importing SQL commands from a .sql file
5. Delete Database
DROP DATABASE <database_name>;
Work with tables
6. View the tables available in the database
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 constraints when using
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.
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:
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 column names.
INSERT INTO <table_name> VALUES (<value1>, <value2>, <value3>, …);
10. Updating table data
UPDATE <table_name> SET <col_name1> = <value1>, <col_name2> = <value2>, ... WHERE <condition>;
11. Delete all data from the table
DELETE FROM <table_name>;
12. Delete table
DROP TABLE <table_name>;
Commands for creating queries
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>;
14. SELECT DISTINCT
The table columns may contain duplicate data. Use
SELECT DISTINCTto get only non-repeating data.
SELECT DISTINCT <col_name1>, <col_name2>, … FROM <table_name>;
You can use the keyword
SELECTto specify conditions in the query:
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).
Try the following commands. Pay attention to the conditions specified in
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
GROUP BYoften used with aggregate functions, such as
AVG, for groups of output values.
SELECT <col_name1>, <col_name2>, … FROM <table_name> GROUP BY <col_namex>;
We derive the number of courses for each faculty:
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;
HAVINGwas added to SQL because it
WHEREcannot be used to work with aggregate functions.
SELECT <col_name1>, <col_name2>, ... FROM <table_name> GROUP BY <column_namex> HAVING <condition>
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
SELECT <col_name1>, <col_name2>, … FROM <table_name> ORDER BY <col_name1>, <col_name2>, … ASC|DESC;
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;
BETWEENUsed to select data values from a specific range. Numeric and text values can be used, as well as dates.
SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <col_namex> BETWEEN <value1> AND <value2>;
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;
The statement is
WHEREto set a search pattern for a similar value.
There are two free operators that are used in
%(none, one or more characters);
SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <col_namex> LIKE <pattern>;
We will display a list of courses whose name contains
«to», and a list of courses whose names begin with
SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE 'CS-___';
With the help
INyou can specify multiple values for the operator
SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <col_namen> IN (<value1>, <value2>, …);
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.’);
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 <col_name1>, <col_name2>, … FROM <table_name1> JOIN <table_name2> ON <table_name1.col_namex> = <table2.col_namex>;
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;
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;
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;
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.
CREATE VIEW <view_name> AS SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <condition>;
DROP VIEW <view_name>;
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
WHEREinvested in another request.
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 );