Skip to main content

The Very Basic 15 SQL Commands That You Should Know

SQL commands are the instructions used to communicate with a database to perform tasks, functions, and queries with data.


In this article, we will be discussing some basic Structured Query Language (SQL) commands with example queries.

Before going into that, let’s see the language elements in a SQL Query.

  1. Keywords — Reserved (SELECT, COUNT, and etc.), or non-reserved (ASC, DOMAIN, KEY, and etc.).
  2. Identifiers — Names on database objects, like tables, columns, and schemas.
  3. Clauses — Sometimes optional. These are the constituent components of statements and queries.
  4. Expressions — This produces either scalar values, or tables consisting of columns and rows of data.
  5. Predicates — This specifies conditions that can be evaluated to SQL three-valued logic such as true/false/unknown.
  6. Operators — Arithmetic operators, Concatenation operator, and Comparison operators.
  7. Queries — An important element that retrieves the data based on specific criteria.
  8. Statements — These may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics. Though not required on every platform, SQL statements also include the semicolon (;) statement terminator which is defined as a standard part of the SQL grammar.
  9. Insignificant whitespace — It is normally ignored in SQL statements and queries, making it easier to format SQL code for readability.

The following is an example of a complete statement with several of its language elements.

It is normally ignored in SQL statements and SQL commands can be used to search the database as well as do other tasks such as creating tables, adding data to tables, changing data, and removing tables.

If you’re going to deal with SQL, you should be familiar with the following fundamental SQL instructions (also known as clauses).


1 — CREATE DATABASE

CREATE DATABASE command is used to create a new MySQL database.

CREATE DATABASE databasename;

Here, let’s create a database with the name ‘pscompany’.

CREATE DATABASE pscompany;

2 — CREATE TABLE

CREATE TABLE does exactly what it says on the tin: it creates a database table. You may give the table a name and define the columns that should be included in it.

CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype
);

Let’s create a table with the name ‘products’.

DROP TABLE IF EXISTS products;
CREATE TABLE products (
 productID    INT  NOT NULL,
 productCode  CHAR(3) NOT NULL,
 name         VARCHAR(30)  NOT NULL,
 quantity     INT NOT NULL,
 price        DECIMAL(10,2) NOT NULL,
 PRIMARY KEY  (productID));

DROP TABLE works as it is. Usually, when we create a new table, it is better to check and drop if any table exists in the same name (be aware, you may need that table if it contains needed information, so better be careful while naming the tables).


3 — INSERT for inserting values inside the created table

INSERT INTO products VALUES
 (column_1 value 1, column_2 value 1, column_3 value 1),
 (column_1 value 2, column_2 value 2, column_3 value 2);

Let’s insert some values inside the ‘products’ table.

INSERT INTO products VALUES
 (1001, 'PEN', 'Pen Red', 5000, '1.23'),
 (1002, 'PEN', 'Pen Blue', 8000, '1.25'),
 (1003, 'PEN', 'Pen Black', 2000, '1.25'),
 (1004, 'PEC', 'Pencil 2B', 10000, '0.48'),
 (1005, 'PEC', 'Pencil 2H', 8000, '0.49'),
 (2001, 'PEC', 'Pencil 3B', 500, '0.52'),
 (2002, 'PEC', 'Pencil 4B', 200, '0.62'),
 (2003, 'PEC', 'Pencil 5B', 100, '0.73'),
 (2004, 'PEC', 'Pencil 6B', 500, '0.47');

4 — ALTER TABLE

ALTER TABLE modifies a table’s structure. You can add or delete columns, build or destroy indexes, alter the type of existing columns, and rename columns or the table itself, for example. You may also modify aspects like the table’s storage engine or the table comment.

ALTER TABLE table_name
 ADD column_name datatype;

Here in this example, we are not going to alter the table. But if you need to change anything, you may use this command to alter the existing table as your want.


5 — CHECK

The CHECK criterion is used to restrict the range of values that may be entered into a column. When you use a CHECK constraint on a single column, only specific values are allowed for that column. A table’s CHECK constraint might limit the values in specific columns based on the values in other columns in the row. When the “products” table is created, the following SQL creates a CHECK constraint on the “price” field. The CHECK restriction guarantees that no items under the price of 0 can be present.

DROP TABLE IF EXISTS products;
CREATE TABLE products (
 productID    INT  NOT NULL,
 productCode  CHAR(3) NOT NULL,
 name         VARCHAR(30)  NOT NULL,
 quantity     INT NOT NULL,
 price        DECIMAL(10,2) NOT NULL,
 CHECK (price>=0),
 PRIMARY KEY  (productID));

6 — SELECT

To retrieve data from a database, use the SELECT command. The data retrieved in a result table is known as the result-set.

SELECT column_1, column_2 FROM table_name

Let’s retrieve all the data from our ‘products’ table.

SELECT * FROM products;

What will happen?

+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
|      2001 | PEC         | Pencil 3B |      500 |  0.52 |
|      2002 | PEC         | Pencil 4B |      200 |  0.62 |
|      2003 | PEC         | Pencil 5B |      100 |  0.73 |
|      2004 | PEC         | Pencil 6B |      500 |  0.47 |
+-----------+-------------+-----------+----------+-------+

You will get the entire table.


7 — Filtering using WHERE (for filters: AND, OR, IN, BETWEEN, and LIKE)

To restrict the number of rows returned, use the WHERE clause.

Now we’ll execute the same SELECT query as before, but this time we’ll use a WHERE statement to limit the number of rows returned.

SELECT * FROM products WHERE quantity>500;

We are selecting only the data that belongs to a quantity greater than 500.

So what will be the result?

+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
+-----------+-------------+-----------+----------+-------+

Earlier, we got 9 rows, and now, we’ve got only 5 rows.


8 — UPDATE

The UPDATE statement is used to update a record in a table. To define which records you wish to update, use the WHERE condition. One or more columns can be updated at the same time.

UPDATE table_name
SET column1 = value1, 
    column2 = value2, ...
WHERE condition;

Let’s try to change the price of 1001 — productID item.

UPDATE products SET price = 2 WHERE productID = 1001;

You must get the following output right next to the execution of the above line of command.

Query OK, 1 row affected (0.965 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It means, 1 row has been updated. Let’s verify the value by retrieving the price of 1001 — productID.

SELECT * FROM products WHERE productID = 1001;

And the output is,

+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     5000 |  2.00 |
+-----------+-------------+---------+----------+-------+

Earlier it was 1.23.


9 — COUNT

COUNT will count the number of rows in the result set and report the count as a column. Here are some examples of how COUNT could be used:

  1. Counting all of a table’s rows (no group by required).
  2. Counting the totals of data subsets (requires a Group By section of the statement).

The count of all rows is provided by this SQL query.

SELECT COUNT(*) FROM products;

And the output is,

+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

10 — GROUP BY

GROUP BY allows you to combine rows and aggregate data.

SELECT column_name, COUNT(*) FROM table_name 
GROUP BY column_name;

Let’s try to group the productCode along with the number of items per code.

SELECT productCode, COUNT(*) FROM products 
GROUP BY productCode;

And the output is,

+-------------+----------+
| productCode | COUNT(*) |
+-------------+----------+
| PEC         |        6 |
| PEN         |        3 |
+-------------+----------+

11 — HAVING

HAVING lets you filter the data gathered by the GROUP BY clause so that the user only sees a subset of the records.

SELECT column_name, COUNT(*) FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

Let’s try the same GROUP BY command but with a HAVING constraint so that we get the productCode of items (more than 5) that are there in the products table.

SELECT productCode, COUNT(*) FROM products GROUP BY productCode HAVING COUNT(*)>5;

And the output is,

+-------------+----------+
| productCode | COUNT(*) |
+-------------+----------+
| PEC         |        6 |
+-------------+----------+

12— AS

AS allows you to rename a column or table using an alias.

Let’s use the same query we used in GROUP BY command.

SELECT productCode AS Code, COUNT(*) AS 'Nos.' FROM products 
GROUP BY productCode;

And the output is,

+------+------+
| Code | Nos. |
+------+------+
| PEC  |    6 |
| PEN  |    3 |
+------+------+

13— ORDER BY

ORDER BY allows us to sort the result set by one or more of the SELECT section’s items. Here is a SQL query that sorts the students in descending order by FullName. The default sort order is ascending (ASC), but you may use DESC to sort in the other direction (descending).

SELECT productCode AS Code, COUNT(*) AS 'Nos.' FROM products 
GROUP BY productCode 
ORDER BY 'Nos.' ASC;

And the output is,

+------+------+
| Code | Nos. |
+------+------+
| PEN  |    3 |
| PEC  |    6 |
+------+------+

14 — DELETE

The DELETE command is used to remove a record from a table. You can remove all of the table’s records or just a handful. Specify which records you wish to remove using the WHERE condition.

DELETE FROM table_name
WHERE condition;

Before deleting, make sure about the foreign key constraints of the particular row that you’re intended to delete.


15 — LIKE

When a column contains a specific pattern of characters, LIKE is used in a WHERE or HAVING (as part of the GROUP BY) to limit the selected rows to the items.

This SQL will select the rows that have name with the letters cil.

SELECT * FROM products WHERE name LIKE '%cil%';

And the output is,

+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
|      2001 | PEC         | Pencil 3B |      500 |  0.52 |
|      2002 | PEC         | Pencil 4B |      200 |  0.62 |
|      2003 | PEC         | Pencil 5B |      100 |  0.73 |
|      2004 | PEC         | Pencil 6B |      500 |  0.47 |
+-----------+-------------+-----------+----------+-------+

Instead of choosing the rows with the string pattern, you may use NOT before LIKE to exclude them.

SELECT * FROM products WHERE name NOT LIKE '%cil%';

And the output is,

+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  2.00 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
+-----------+-------------+-----------+----------+-------+

Apart from these, there are JOINS which we have already discussed in a previous article.

Hope this helps. Share your thoughts too.

Labels

Labels:
Location: Colombo, Sri Lanka

Comments

Post a Comment

Popular posts from this blog

Parallel A* Search on GPU

A* search is a fundamental topic in Artificial Intelligence. In this article, let’s see how we can implement this marvelous algorithm in parallel on Graphics Processing Unit (GPU). Traditional A* Search Classical A* search implementations typically use two lists, the open list, and the closed list, to store the states during expansion. The closed list stores all of the visited states and is used to prevent the same state from being expanded multiple times. To detect duplicated nodes, this list is frequently implemented by a linked hash table. The open list normally contains states whose successors have not yet been thoroughly investigated. The open list’s data structure is a priority queue, which is typically implemented by a binary heap. The open list of states is sorted using the heuristic function  f(x) : f(x) = g(x) + h(x). The distance or cost from the starting node to the current state  x  is defined by the function  g(x) , and the estimated distance or co...
Post a Comment
Read more

Multiclass Classification Using Support Vector Machines

Binary Classification The machine should only categorize an instance as one of two classes of this type: yes/no, 1/0, or true/false. In this sort of categorization inquiry, the answer is always yes or no. Is there a human in this photograph, for example? Is there a good tone to this text? Will the price of a specific stock rise in the coming month? Multiclass Classification In this case, the machine must categorize an instance into only one of three or more classes. Multiclass categorization may be shown in the following examples: Positive, negative, or neutral classification of a text Identifying a dog breed from a photograph A news item might be classified as sports, politics, economics, or social issue. Support Vector Machines (SVM) SVM is a supervised machine learning method that may be used to aid with both classification and regression problems. It aims to determine the best border (hyperplane) between distinct classes. In basic terms, SVM performs complicated data modificati...
Post a Comment
Read more

A 3000 Years Old Love Story

Pharaoh Ramesses the Great and Queen Nefertari Pharaoh Ramesses II the Great ruled ancient Egypt during the 19th dynasty (1292-1190 BCE). His reign was the second-longest in Egyptian history, lasting from 1279 to 1213 BCE. He assumed the throne in 1279 BC as a royal member of the Nineteenth Dynasty and ruled for 67 years. In Greek sources, Ramesses II was also known as Ozymandias, with the first half of the appellation deriving from Ramesses' regnal name, Usermaatre Setepenre, which means 'The Maat of Ra is mighty, Chosen of Ra'.  He is also recognized as the Egyptian Empire's greatest, most renowned, and most dominating pharaoh. His successors and subsequent Egyptians are reported to have referred to him as the Great Ancestor. Ramesses II was a famous explorer, monarch, and warrior who conducted multiple military excursions to the Levant to reestablish Egyptian dominance over Canaan. He is also supposed to have conducted journeys south to Nubia, which are documented in...
Post a Comment
Read more