top of page

SQL & RDBMS

 

SQL

  • SQL stands for Structured Query Language

  • SQL lets you access and manipulate databases

  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

  • Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

  • However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

 

 

RDBMS

  • RDBMS stands for Relational Database Management System.

  • RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

  • The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

 

Tables, Rows, and Columns

  • The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

  • Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, ZipCode and Country. A field is a column in a table that is designed to maintain specific information about every record in the table.

  • A record, also called a row, is each individual entry that exists in a table. For example, there are 5 records in the below Customers table. A record is a horizontal entity in a table.

  • A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Primary Key and Foreign Key

​

In order for a table to qualify as a relational table, it must have a primary key. The primary key consists of one or more columns whose data contained within are used to uniquely identify each row in the table.  You can think of them as an address.  If the rows in a table were mailboxes, then the primary key would be the listing of street addresses.

 

When a primary key is composed of multiple columns, the data from each column is used to determine whether a row is unique.

In order to be a primary key, several conditions must hold true.  First, as we mentioned, the columns must be unique.  To clarify, we’re referring to the data within the rows, not the column names themselves.  Also, no value in the columns can be blank or NULL.

 

The primary key for each table is stored in an index.  The index is used to enforce the uniqueness requirement.

 

A foreign key is a set of one or more columns in a table that refers to the primary key in another table.  There aren’t any special code, configurations, or table definitions you need to place to officially “designate” a foreign key.

 

Summary:

  • Primary Key ( Unique and Not Null)

  • Foreign Key (primary key of another table)

 

SQL Syntax:

​

1. SQL SELECT Statement

 

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

 

SELECT Syntax:

 

SELECT column1, column2, ...FROM table_name;

 

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

 

SELECT * FROM table_name;

 

The following SQL statement selects all the columns from the "Customers" table:

SELECT * FROM Customers;

 

 

2. The SQL SELECT DISTINCT Statement

 

 

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

 

SELECT DISTINCT Syntax:

 

SELECT DISTINCT column1, column2, ...FROM table_name;

 

The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

SELECT DISTINCT Country FROM Customers;

 

 

3. The SQL WHERE Clause

 

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

 

WHERE Syntax:

 

SELECT column1, column2, ...FROM table_name WHERE condition;

 

The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

SELECT * FROM Customers WHERE Country='Mexico';

 

 

The following operators can be used in the WHERE clause:

 

4. The SQL AND, OR and NOT Operators

 

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

  • The AND operator displays a record if all the conditions separated by AND are TRUE.

  • The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

 

AND Syntax:

 

SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;

 

The following SQL statement selects all fields from "Customers" where the country is "Germany" AND the city is "Berlin":

SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';

 

OR Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;

 

The following SQL statement selects all fields from "Customers" where the city is "Berlin" OR "London":

SELECT * FROM Customers WHERE City='Berlin' OR City='London';

 

NOT Syntax:

SELECT column1, column2, ... FROM table_name WHERE NOT condition;

 

The following SQL statement selects all fields from "Customers" where the country is NOT "Germany":

SELECT * FROM Customers WHERE NOT Country='Germany';

 

5. The SQL ORDER BY Keyword

 

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

 

ORDER BY Syntax:

 

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

 

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

SELECT * FROM Customers ORDER BY Country DESC;

 

 

6. The SQL COUNT(), AVG() and SUM() Functions

 

The COUNT() function returns the number of rows that matches specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

 

COUNT() Syntax:

 

SELECT COUNT(column_name) FROM table_name WHERE condition;

 

The following SQL statement finds the number of products:

SELECT COUNT(ProductID) FROM Products;

 

AVG() Syntax:

 

SELECT AVG(column_name) FROM table_name WHERE condition;

 

The following SQL statement finds the average price of all products:

SELECT AVG(Price) FROM Products;

 

SUM() Syntax:

 

SELECT SUM(column_name) FROM table_name WHERE condition;

 

The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:

SELECT SUM(Quantity) FROM OrderDetails;

 

7. The SQL LIKE Operator

 

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

  • % - The percent sign represents zero, one, or multiple characters

  • _ - The underscore represents a single character

  • The percent sign and the underscore can also be used in combinations

 

LIKE Syntax:

 

SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

​

 

The following SQL statement selects all customers with a CustomerName starting with "a":

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

 

 

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

 

 

 

8. The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included. 

​

BETWEEN Syntax:

​

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

​

The following SQL statement selects all products with a price BETWEEN 10 and 20:

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

​

9. SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:​

Then, look at a selection from the "Customers" table:

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

​

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

​

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables

  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

img_innerjoin.gif
img_leftjoin.gif
img_rightjoin.gif
img_fulljoin.gif
bottom of page