13
DecCyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
If you are learning SQL or among those who want to improve your SQL language then you should start practicing SQL right away. By solving practice questions one can easily learn the language and get familiarized with it. Here, we have brought many imperative queries and that can help you in learning SQL in an easy manner. Here we have also listed the questions that are asked by top interviewers of top IT industries. You can get real-time experience by reading these questions. We would recommend that before reading the solution try to write your own queries for the given question. Moreover, you can also prepare your test database to test the queries. The following queries can help you in getting familiar with the SQL platform and its syntax along with improving your knowledge.
Suppose you have a database schema, named My_Schema and you want to see all tables of this schema, then you may use the following query: Select * From My_Schema.Tables;
This is one of the most used SQL queries. Following is the example to retrieve the Student_ID from the Student table: Select Student_ID from Student;
If you want to display all columns from the Student table then you can use the following query: Select * From Student;
If there is a constraint to retrieve data from a table then you can use the SQL query in the following manner: Select Student_ID from Student Where Student_ID=’0000’;
Read: What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)
To order the query result with respect to any referencing attribute you can use ‘Order By’ clause. Like for any numeric value column that has integer type values, you can get the query result either in ascending or descending order, while in case of String data type columns the result will be displayed in alphabetical order. Select Student_ID, Last_Name From Student Where City=’Delhi’ ORDER BY Student_ID;
You can also use either “asc” or “desc” to sort the query result either in ascending or descending order, as shown in the below example: Select Student_ID, Last_Name From Student Where City=’Delhi’ ORDER By Student_ID asc;
GROUP BY property of clause of SQL can group the resulting data as per specified condition like shown in the below example: Select First_Name, Age from Student Where Age>15 GROUP BY Age ORDER BY First_Name;
Moreover one can also use built-in Math functions like AVG or COUNT either to know the average of age or to count the number of results respectively.
Through the following query, you can know the total number of students that are counted by Student_ID attribute values. Moreover here the Grouping of the students is being done by City: Select COUNT(Student_ID), City From Student GROUP BY City
;
Learn SQL Server in the Easiest Way
It easily helps in finding the average of numbers. In the following query the average salary of the employees will be calculated and display: Select AVG(Salary) From Employees;
Through SUM the total of the attribute’s value is calculated and the attribute is passed as a function argument: Select SUM(Salary) From Employee Where Emp_Age>30;
To know all present and saved views in database schema one can use the following query: Select * From My_Schema.views;
Read: SQL Server on the Cloud - It is not that Cloudy
A view of the table can be created through a single query. Views have rows and columns like other tables. You can also run queries as independent views as through views you can retrieve the query result later and need not to compute them every time. You need not compute the same query result query every time. With the help of the following query you can create the views: Create View Passing_Student As Select First_Name, Student_ID From Student Where GPA>60;
Just like tables, one can select the attributes from views as well in the following manner: Select * From Passing_Student;
Update View This query will update the view named Employee List and if it will not exist then will be created as specified in the query itself: Create OR Replace View [Employee List] AS Select Employee_ID, EmployeeName, Department From Employees Where Discontinued=No;
Drop a View Through the following query a view will be dropped or deleted: Drop View V1;
SQL Server Training & Certification
A user-defined table is a collection of defined information and they can even be used as arguments for user-defined procedures as well. If you want to use the tables as arguments then the following command can help you in keeping track of these tables: Select * From Sys
.Objects Where Type=”u”;
The primary key can identify the table values uniquely. Through the following query can enlist all fields of the table that are used as Primary Keys: Select * from Sys
.Objects Where Type=’PK’;
The unique key can make sure that all values of any column are different. Through the following query you can know the table columns that are set as unique keys: Select * From Sys
.Objects Where Type=’uq’;
Read: SSRS Sub Reports and deployment process-How to do it
Foreign keys are used to link two tables. Foreign keys are the attributes that are basically the primary keys of the table: Select * From Sys
.Object Where Type=’f’;
The trigger is like an event-listener that is basically a pre-defined set of instructions that gets executed when certain instructions are followed or conditions are satisfied. You can check and see the list of all defined triggers in the database through the following query: Select * From Sys
.Objects Where Type=’tr’;
Read: How to Get Your Career as an SSIS Developer Rolling?
Suppose you have a table and by mistake, the two columns of the table have been transposed and the values of one column are entered in another column of the same data type. In below example, the two columns of the Customer table have been transposed they are Phone and Zip code. Through the following SQL command we can easily fix the problem: Update Customer Set Zip=Phone, Phone=Zip;
If you want to find out a date or any value between two consecutive pre-defined values then you can use his query, e.g. following query is used to select all order IDs from Order table that are placed between 02/01/2019 and 02/02/2019: Select Order_ID from Orders Where Data BETWEEN ‘02/01/2019’ And ‘02/02/2019’;
SQL Server Training & Certification
Suppose we have to find the matching records from two tables then we can use JOIN statement of SQL query. The JOIN statements can make the task easier. Through the following query, all the matching records of two tables Customers and Orders will be displayed. SELECT Customer_ID From Customer INNER JOIN Orders ON Customer_ID =Orders_ID;
Here you can use various types of Joins like Inner Join, Outer Join, Full Join, Left and Right Joins.
To know more about how to implement SQL in the workplace, avail our self-learning courses at a huge discount.
Internal tables are created by user-actions and are not accessible. You can view metadata of an internal table that cannot be manipulated through the following query: Select * From Sys
.Objects Where Type=’it’;
Read: What Is The Difference Between The SQL Inner Join And Outer Joins?
Stored procedures of SQL are the group of SQL statements or queries, that are grouped together to perform a particular task. Through the following query you can know all the procedures of your database: Select * From Sys
.Objects Where Type=’p’;
These are some of the most important and used SQL queries. You can study and practice these. You can share your thoughts regarding the same in the comments section below.
If you have any doubts about how online training classes are conducted, register for a demo class and get familiar with the online learning environment right away.
Read: SQL Schema | Learn How to Create, Alter and Drop Schema in SQL Effectively!FaceBook Twitter LinkedIn Pinterest Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
MySQL vs SQL Server vs Oracle: A Detailed Comparison 592.6k
How to Become a Database Administrator? Just Know the 5 Steps 5.9k
SQL Having Clause Functions 7.2k
Difference Between Clustered and Non-Clustered Index in the SQL 207.1k
Top 97 Data Modeling Interview Questions and How To Answer Them 629.1k
Receive Latest Materials and Offers on SQL Server Course
Interviews