Database queries which are used day to day programming and its understanding.

SQL is incredibly powerful, and like every well-made development tool, it has a few commands which are used by developers in day to day practice. Here are a few of the most important ones – each of these queries is consequential to almost every system that interacts with an SQL database.

Some of The Most Important SQL Commands

  • SELECT – Extracts data from a database
  • UPDATE – Updates data in a database
  • DELETE – Deletes data from a database
  • INSERT INTO – Inserts new data into a database
  • CREATE DATABASE – Creates a new database
  • ALTER DATABASE – Modifies a database
  • CREATE TABLE – Creates a new table
  • ALTER TABLE – Modifies a table
  • DROP TABLE – Deletes a table
  • CREATE INDEX – Creates an index (search key)
  • DROP INDEX – Deletes an index

1. The query for Retrieving Tables

This query can be run to retrieve the list of tables present in a database where the database is “My_Schema”.
SELECT * FROM My_Schema.Tables;

2. The query for Selecting Columns from a Table

This is perhaps the most widely used of SQL queries examples. In the example below, we are extracting the “Student_ID” column or attribute from the table “STUDENT.”
SELECT Student_ID FROM STUDENT;
If you want to display all the attributes from a particular table, this is the right query to use:
SELECT * FROM STUDENT;

3. The query for Outputting Data Using a Constraint

This SQL query retrieves the specified attributes from the table on the constraint Employee ID =0000
SELECT EMP_ID, NAME FROM EMPLOYEE_TBL WHERE EMP_ID = ‘0000’;

4. The query for Outputting Sorted Data Using ‘Order By’

This query orders the results with respect to the attribute which is referenced to using “Order By” – so for example, if that attribute is an integer data type, then the result would either be sorted in ascending or descending order; likewise, if the data type is a String then the result would be ordered in alphabetical order.

  1. ELECT EMP_ID, LAST_NAME FROM EMPLOYEE
  2. WHERE CITY = ‘Seattle’ ORDER BY EMP_ID;

The ordering of the result can also be set manually, using “asc ” for ascending and “desc” for descending.

  1. SELECT EMP_ID, LAST_NAME FROM EMPLOYEE_TBL
  2. WHERE CITY = ‘INDIANAPOLIS’ ORDER BY EMP_ID asc;

5. The query for Outputting Sorted Data Using ‘Group By’

The ‘Group By’ property groups the resulting data according to the specified attribute.

  1. SELECT Name, Age FROM Patients WHERE Age > 40
  2. GROUP BY Age ORDER BY Name;

SQL Queries for Data Manipulation Using Math Functions

There are a lot of built-in math functions like COUNT and AVG which provide basic functionalities of counting the number of results and averaging them respectively.

6. Data Manipulation Using COUNT

This query displays the total number of customers by counting each customer ID. In addition, it groups the results according to the country of each customer.

  1. SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

7. Data Manipulation Using SUM

SUM calculates the total of the attribute that is given to it as an argument.

  1. SELECT SUM(Salary)FROM Employee WHERE Emp_Age < 30;

8. Data Manipulation Using AVG

Simple – an average of a given attribute.

  1. SELECT AVG(Price)FROM Products;

9. The query for Listing all Views

This SQL query lists all the views available in the schema.

  1. SELECT * FROM My_Schema.views;

10. The query for Creating a View

A view is a tailored table that is formed as a result of a query. It has tables and rows just like any other table. It’s usually a good idea to run queries in SQL as independent views because this allows them to be retrieved later to view the query results, rather than computing the same command every time for a particular set of results.

  1. CREATE VIEW Failing_Students AS
  2. SELECT S_NAME, Student_ID
  3. FROM STUDENT
  4. WHERE GPA > 40;

11. The query for Retrieving a View

The standard syntax of selecting attributes from a table is applicable to views as well.

  1. SELECT * FROM Failing_Students;

12. The query for Updating a View

This query updates the view named ‘Product List’ – and if this view doesn’t exist, then the Product List view gets created as specified in this query.

  1. CREATE OR REPLACE VIEW [ Product List] AS
  2. SELECT ProductID, ProductName, Category
  3. FROM Products
  4. WHERE Discontinued = No;

13. The query for Dropping a View

This query will drop or delete a view named ‘V1’.

  1. DROP VIEW V1;

14. Query to Display User Tables

A user-defined table is a representation of defined information in a table, and they can be used as arguments for procedures or user-defined functions. Because they’re so useful, it’s useful to keep track of them using the following query.

  1. SELECT * FROM Sys.objects WHERE Type=’u’

15. Query to Display Primary Keys

A primary key uniquely identifies all values within a table. The following SQL query lists all the fields in a table’s primary key.

  1. SELECT * FROM Sys.Objects WHERE Type=’PK’

16. The query for Displaying Unique Keys

A Unique Key allows a column to ensure that all of its values are different.

  1. SELECT * FROM Sys.Objects WHERE Type=’uq’

17. Displaying Foreign Keys

Foreign keys link one table to another – they are attributes in one table which refer to the primary key of another table.

  1. SELECT * FROM Sys.Objects WHERE Type=’f’

18. Displaying Triggers

A Trigger is sort of an ‘event listener’ – i.e, it’s a pre-specified set of instructions that execute when a certain event occurs. The list of defined triggers can be viewed using the following query.

  1. SELECT * FROM Sys.Objects WHERE Type=’tr’

19. Displaying Internal Tables

Internal tables are formed as a by-product of a user-action and are usually not accessible. The data in internal tables cannot be manipulated; however, the metadata of the internal tables can be viewed using the following query.

  1. SELECT * FROM Sys.Objects WHERE Type=’it’

20. Displaying a List of Procedures

A stored procedure is a group of SQL queries that logically form a single unit and perform a particular task. Thus, using the following query you can keep track of them:

  1. SELECT * FROM Sys.Objects WHERE Type=’p’