SQL: Fully Qualified Names

In SQL, a fully qualified name refers to the complete and unambiguous name of a database object, typically consisting of the object name and the schema or database name it belongs to. Fully qualified names help identify and locate database objects accurately, especially when dealing with objects that have the same name but exist in different schemas or databases. Here’s an explanation with an example:

Consider two tables with the same name “employees” in different schemas: “hr” and “sales”. Each table contains employee information, but they are distinct and belong to separate schemas.

To refer to the “employees” table in the “hr” schema, you would use a fully qualified name as follows:

SELECT * FROM hr.employees;

To refer to the “employees” table in the “sales” schema, you would use a fully qualified name as follows:

SELECT * FROM sales.employees;

In these examples, the fully qualified names include the schema name (hr or sales) followed by a dot (.) and the table name (employees). This ensures that the query explicitly identifies which table is being referred to, preventing any ambiguity.

Fully qualified names are particularly useful in scenarios where there are naming conflicts, such as when multiple tables, views, or other objects have the same name but exist in different schemas or databases. By specifying the fully qualified name, you can differentiate and access the specific object you intend to work with.

It’s important to note that the specific syntax for fully qualified names may vary depending on the database management system (DBMS) you are using. Some DBMSs may use different delimiters or require additional qualifiers like the database name. Consult the documentation or resources specific to your DBMS for accurate syntax and usage details.

SQL: ORDER BY Explaining With Example

In SQL, the ORDER BY clause is used to sort the result set of a query based on one or more columns. It allows you to specify the order in which the rows should be returned. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and age. Here’s a sample data:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 1  | John       | Smith     | 28  |
| 2  | Jane       | Doe       | 32  |
| 3  | Alex       | Johnson   | 24  |
| 4  | Lisa       | Brown     | 30  |
+----+------------+-----------+-----+

To sort the rows of this table based on a specific column, you can use the ORDER BY clause in the SELECT statement. Here’s an example:

SELECT * FROM employees ORDER BY last_name;

Output:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 4  | Lisa       | Brown     | 30  |
| 3  | Alex       | Johnson   | 24  |
| 2  | Jane       | Doe       | 32  |
| 1  | John       | Smith     | 28  |
+----+------------+-----------+-----+

In this example, the SELECT statement retrieves all rows from the “employees” table, and the ORDER BY clause is used to sort the result set based on the “last_name” column in ascending order. The rows are arranged alphabetically based on the last names.

You can also specify multiple columns in the ORDER BY clause to sort the result set based on multiple criteria. For example:

SELECT * FROM employees ORDER BY last_name, first_name;

Output:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 4  | Lisa       | Brown     | 30  |
| 2  | Jane       | Doe       | 32  |
| 3  | Alex       | Johnson   | 24  |
| 1  | John       | Smith     | 28  |
+----+------------+-----------+-----+

In this case, the result set is sorted first by the “last_name” column and then by the “first_name” column. If two rows have the same last name, they are further sorted based on the first name.

The ORDER BY clause is essential for controlling the order of rows in the result set, and it can be used to sort in ascending (default) or descending order by specifying the ASC or DESC keyword, respectively.

SELECT * FROM employees ORDER BY age DESC;

Output:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 2  | Jane       | Doe       | 32  |
| 4  | Lisa       | Brown     | 30  |
| 1  | John       | Smith     | 28  |
| 3  | Alex       | Johnson   | 24  |
+----+------------+-----------+-----+

In this example, the result set is sorted based on the “age” column in descending order.

The ORDER BY clause is flexible and allows you to sort the result set based on one or more columns in different orders to meet your specific requirements.

SQL: Sorting multiple columns with Example

To sort a result set based on multiple columns in SQL, you can specify multiple columns within the ORDER BY clause. The rows will be sorted first by the first specified column, then by the second column, and so on. Here’s an example:

Consider a table named “employees” with columns: id, first_name, last_name, and age. Here’s a sample data:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 1  | John       | Smith     | 28  |
| 2  | Jane       | Doe       | 32  |
| 3  | Alex       | Johnson   | 24  |
| 4  | Lisa       | Brown     | 30  |
+----+------------+-----------+-----+

To sort the rows of this table based on multiple columns, you can use the ORDER BY clause with multiple columns. Here are some examples:

Sorting by Last Name and First Name:

   SELECT * FROM employees ORDER BY last_name, first_name;

Output:

   +----+------------+-----------+-----+
   | id | first_name | last_name | age |
   +----+------------+-----------+-----+
   | 4  | Lisa       | Brown     | 30  |
   | 2  | Jane       | Doe       | 32  |
   | 3  | Alex       | Johnson   | 24  |
   | 1  | John       | Smith     | 28  |
   +----+------------+-----------+-----+

In this example, the result set is sorted first by the “last_name” column, and if there are any duplicate last names, the rows are further sorted by the “first_name” column.

Sorting by Age in Descending Order, then by Last Name in Ascending Order:

   SELECT * FROM employees ORDER BY age DESC, last_name;

Output:

   +----+------------+-----------+-----+
   | id | first_name | last_name | age |
   +----+------------+-----------+-----+
   | 2  | Jane       | Doe       | 32  |
   | 4  | Lisa       | Brown     | 30  |
   | 1  | John       | Smith     | 28  |
   | 3  | Alex       | Johnson   | 24  |
   +----+------------+-----------+-----+

In this example, the result set is sorted first by the “age” column in descending order. If there are any duplicate ages, the rows are then sorted by the “last_name” column in ascending order.

By specifying multiple columns in the ORDER BY clause and defining the desired sorting order for each column, you can control the sorting behavior and achieve the desired result set ordering based on multiple criteria.

SQL: Where Statement with example

In SQL, the WHERE statement is used to filter rows from a table based on specified conditions. It allows you to specify criteria that must be met for a row to be included in the query result. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and age. Here’s a sample data:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 1  | John       | Smith     | 28  |
| 2  | Jane       | Doe       | 32  |
| 3  | Alex       | Johnson   | 24  |
| 4  | Lisa       | Brown     | 30  |
+----+------------+-----------+-----+

To filter the rows of this table based on specific conditions, you can use the WHERE statement in the SELECT statement. Here’s an example:

SELECT * FROM employees WHERE age > 25;

Output:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 1  | John       | Smith     | 28  |
| 2  | Jane       | Doe       | 32  |
| 4  | Lisa       | Brown     | 30  |
+----+------------+-----------+-----+

In this example, the SELECT statement retrieves all rows from the “employees” table where the “age” column is greater than 25. The WHERE clause specifies the condition “age > 25”, and only the rows that satisfy this condition are included in the result set.

You can use various operators in the WHERE statement to construct different conditions, such as:

Comparison operators (e.g., “=”, “<“, “>”, “<=”, “>=”) to compare column values with specific values.
Logical operators (e.g., “AND”, “OR”, “NOT”) to combine multiple conditions.
Pattern matching operators (e.g., “LIKE”, “IN”) to match values based on patterns or specific lists.

For example:

SELECT * FROM employees WHERE age > 25 AND department = 'Sales';

Output:

+----+------------+-----------+-----+
| id | first_name | last_name | age |
+----+------------+-----------+-----+
| 1  | John       | Smith     | 28  |
+----+------------+-----------+-----+

In this example, the SELECT statement retrieves rows where the “age” column is greater than 25 and the “department” column is equal to ‘Sales’.

The WHERE statement is a powerful tool to filter and retrieve specific rows from a table based on conditions. It allows you to narrow down the result set and obtain the data that meets your specific criteria.

amit verma

Website | + posts

Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all