Ultimate List of 20 Important SQL Queries

Ultimate List of 20 Important SQL Queries

  February 03, 2018    Selva

---

Ultimate List of 20 Important SQL Queries

SQL is incredibly powerful, and like every well-made development tool, it has a few commands which it’s vital for a good developer to know.

1.    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”.

1	SELECT * FROM My_Schema.Tables;

2.    Query for Selecting Columns from a Table

 This is perhaps the most widely used SQL query. In the example below, we are extracting the “Student_ID” column or attribute from the table “STUDENT”.

1	SELECT Student_ID FROM STUDENT;

If you want to display all attributes from a particular table, this is the right query to use:

1	SELECT * FROM STUDENT;

3.    Query for Outputting Data Using a Constraint

 This query retrieves the specified attributes from the table on the constraint; Employee ID =0000.

1	SELECT EMP_ID, NAME FROM EMPLOYEE_TBL WHERE EMP_ID = '0000';

4.    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 datatype is a String then the result would be ordered in alphabetical order.

1	SELECT EMP_ID, LAST_NAME FROM EMPLOYEE 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 WHERE CITY = 'INDIANAPOLIS' ORDER BY EMP_ID asc;

5.    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 GROUP BY Age ORDER BY Name;

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.    Query for Listing all Views

 This query lists all the views available in the schema.

1	SELECT * FROM My_Schema.views;

10.       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 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 2 3 4

CREATE VIEW Failing_Students AS SELECT S_NAME, Student_ID FROM STUDENT WHERE GPA > 40;

11.       Query for Retrieving a View

 The standard syntax of selecting attributes from a table is applicable to views as well.
                    SELECT * FROM Failing_Students;

12.       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 2 3 4

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

13.       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 query lists all the fields in a table’s primary key.

1	select * from Sys.Objects where Type='PK'

16.       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'