assignment on sql queries

Learn SQL Queries – Database Query Tutorial for Beginners

SQL stands for Structured Query Language and is a language that you use to manage data in databases. SQL consists of commands and declarative statements that act as instructions to the database so it can perform tasks.

You can use SQL commands to create a table in a database, to add and make changes to large amounts of data, to search through it to quickly find something specific, or to delete a table all together.

In this article, we'll look at some of the most common SQL commands for beginners and how you can use them to effectively query a database – that is, make a request for specific information.

The Basic Structure of a Database

Before we get started, you should understand the hierarchy of a database.

An SQL database is a collection of related information stored in tables. Each table has columns that describe the data in them, and rows that contain the actual data. A field is a single piece of data within a row. So to fetch the desired data we need to get specific.

For example, a remote company can have multiple databases. To see a full list of their databases, we can type SHOW DATABASES; and we can zone in on the Employees database.

The output will look something like this:

A single database can have multiple tables. Taking the example from above, to see the different tables in the employees database, we can do SHOW TABLES in employees; . The tables can be Engineering , Product , Marketing , and Sales for the different teams the company has.

All tables then consist of different columns that describe the data.

To see the different columns use Describe Engineering; . For example the Engineering table can have columns that define a single attribute like employee_id , first_name , last_name , email , country , and salary .

Here's the output:

Tables also consist of rows, which are individual entries into the table. For example a row would include entries under employee_id , first_name , last_name , email , salary , and country . These rows would define and provide info about one person on the Engineering team.

Basic SQL Queries

All the operatations that you can do with data follow the CRUD acronym.

CRUD stands for the 4 main operations we perform when we query a database: Create, Read, Update, and Delete.

We CREATE information in the database, we READ /Retrieve that information from the database, we UPDATE /manipulate it, and if we want we can DELETE it.

Below we'll look at some basic SQL queries along with their syntax to get started.

SQL CREATE DATABASE Statement

To create a database named engineering , we can use the following code:

SQL CREATE TABLE Statement

This query creates a new table inside the database.

It gives the table a name, and the different columns we want our table to have are also passed in.

There are a variety of datatypes that we can use. Some of the most common ones are: INT , DECIMAL , DATETIME , VARCHAR , NVARCHAR , FLOAT , and BIT .

From our example above, this could look like the following code:

The table we create from this data would look something similar to this:

SQL ALTER TABLE Statement

After creating the table, we can modify it by adding another column to it.

For example, if we wanted we could add a birthday column to our existing table by typing:

Now our table will look like this:

SQL INSERT Statement

This is how we insert data into tables and create new rows. It's the C part in CRUD.

In the INSERT INTO part, we can specify the columns we want to fill with information.

Inside VALUES goes the information we want to store. This creates a new record in the table which is a new row.

Whenever we insert string values, they are enclosed in single quotes, '' .

For example:

The table would now look like this:

SQL SELECT Statement

This statement fetches data from the database. It is the R part of CRUD.

From our example earlier, this would look like the following:

The SELECT statement points to the specific column we want to fetch data from that we want shown in the results.

The FROM part determines the table itself.

Here's another example of SELECT :

The asterisk * will grab all the information from the table we specify.

SQL WHERE Statement

WHERE allows us to get more specific with our queries.

If we wanted to filter through our Engineering table to search for employees that have a specific salary, we would use WHERE .

The table from the previous example:

Would now have the output below:

This filters through and shows the results that satisfy the condition – that is, it shows only the rows of the people whose salary is more than 1500 .

SQL AND , OR , and BETWEEN Operators

These operators allow you to make the query even more specific by adding more criteria to the WHERE statement.

The AND operator takes in two conditions and they both must be true in order for the row to be shown in the result.

The OR operator takes in two conditions, and either one must be true in order for the row to be shown in the result.

The BETWEEN operator filters out within a specific range of numbers or text.

We can also use these operators in combination with each other.

Say our table was now like this:

If we used a statement like the one below:

We'd get this output:

This selects all comlumns that have an employee_id between 3 and 7 AND have a country of Germany.

SQL ORDER BY Statement

ORDER BY sorts by the columns we mentioned in the SELECT statement.

It sorts through the results and presents them in either descending or ascending alphabetical or numerical order (with the default order being ascending).

We can specify that with the command: ORDER BY column_name DESC | ASC .

In the example above, we sort through the employees' salaries in the engineering team and present them in descending numerical order.

SQL GROUP BY Statement

GROUP BY lets us combine rows with identical data and similarites.

It is helpful to arrange duplicate data and entries that appear many times in the table.

Here COUNT(*) counts each row separately and returns the number of rows in the specified table while also preservering duplicate rows.

SQL LIMIT Statement

LIMIT lets you spefify the maximum number of rows that should be returned in the results.

This is helpful when working through a large dataset which can cause queries to take a long time to run. By limiting the results you get, it can save you time.

SQL UPDATE Statement

This is how we update a row in a table. It's the U part of CRUD.

The WHERE condition specifies the record you want to edit.

Our table from before:

Would now look like this:

This updates the country of residence column of an employee with an id of 1.

We can also update information in a table with values from another table with JOIN .

SQL DELETE Statement

DELETE is the D part of CRUD. It's how we delete a record from a table.

The basic syntax looks like this:

For instance, in our engineering example that could look like this:

This deletes the record of an employee in the engineering team with an id of 2.

SQL DROP COLUMN Statement

To remove a specific column from the table we would do this:

SQL DROP TABLE Statement

To delete the whole table we can do this:

In this article we went over some of the basic queries you'll use as a SQL beginner.

We learned how to create tables and rows, how to gather and update information, and finally how to delete data. We also mapped the SQL queries to their corresponding CRUD actions.

Thanks for reading and happy coding!

Read more posts .

If this article was helpful, share it .

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

Revising the Select Query I Easy SQL (Basic) Max Score: 10 Success Rate: 95.95%

Revising the select query ii easy sql (basic) max score: 10 success rate: 98.68%, select all easy sql (basic) max score: 10 success rate: 99.54%, select by id easy sql (basic) max score: 10 success rate: 99.66%, japanese cities' attributes easy sql (basic) max score: 10 success rate: 99.59%, japanese cities' names easy sql (basic) max score: 10 success rate: 99.52%, weather observation station 1 easy sql (basic) max score: 15 success rate: 99.42%, weather observation station 3 easy sql (basic) max score: 10 success rate: 97.99%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.72%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.36%, cookie support is required to access hackerrank.

Seems like cookies are disabled on this browser, please enable them to open this website

Where to Practice SQL

• sql practice
• sql queries

Table of Contents

1. Bridging the Theory-Application Divide

2. building ‘muscle memory’, 3. gaining confidence, 4. error handling and troubleshooting, 5. staying updated, 6. real-world application, 1. learnsql.com, 2. leetcode, 3. hackerrank, now you know where to practice sql online ….

Are you looking to boost your data management or analytical abilities? Then you need to know how and where to practice SQL, the worldwide language for managing and running relational databases.

Of course, knowing where to practice SQL can be a challenge – there are so many options! In this article, we’ll walk you through the best digital platforms for enhancing your SQL skills.

SQL, or Structured Query Language, is the universal language for managing and operating relational databases . This is the language that allows computers (and humans) to communicate with databases, allowing for efficient data storage, retrieval, and manipulation.

You might be wondering why this is important. In today's world, relational database management and operations are vital. They provide for the smooth and ordered handling of data, allowing businesses and organizations to make informed decisions. Whether it's a multinational firm tracking sales, a healthcare provider maintaining patient records, or an ecommerce store analyzing client information – SQL is essential to making it all happen. Its impact spans diverse industries, from finance to healthcare to e-commerce and beyond.

When it comes to learning SQL, there's no better place to start than with Learn SQL.com. We have courses tailored to beginners (our SQL Basics course), intermediate users ( SQL Practice track and specialized tracks for reporting and data analysis) and seasoned IT professionals (check out our Advanced SQL track).

The Importance of Hands-On Practice in SQL Learning

Theoretical knowledge is a valuable starting point, but true SQL proficiency is achieved through hands-on practice. Here's why:

Understanding SQL theory – including database design principles and query syntax – is essential. However, knowledge alone won't make you proficient. Actual querying experience bridges the gap between theory and application, enabling you to translate your knowledge into practical solutions.

Just like learning a musical instrument or a sport, SQL proficiency benefits from muscle memory. When you actively engage with SQL commands and queries, you reinforce your learning and create the instinctive knowledge required to do jobs quickly. It's the difference between knowing how to play a single note on the piano and being able to play a full piece with ease.

SQL practice increases confidence. Working through real-world scenarios and problem-solving exercises will give you the confidence you need to handle difficult database tasks in your professional career.

When confronted with complex challenges, you will be confident in your ability to solve them.

Real-world SQL involves dealing with errors and unforeseen challenges. Hands-on practice exposes you to these difficulties and teaches you how to effectively identify, troubleshoot, and fix them. Learning from errors is an important component of SQL mastery.

SQL, like many other technologies, changes with time. It’s important to stay to date on the most recent advancements and best practices. Taking an interactive online course ensures that your skills remain relevant in the ever-changing database management market.

SQL is not an isolated skill; it’s a tool utilized in a variety of businesses. Hands-on practice enables you to apply SQL to real-world scenarios in your chosen industry, such as business research, healthcare data management , or e-commerce optimization. This experience is priceless.

So, whether you're a beginner or seeking to improve your SQL skills, remember that practice makes perfect! The next thing you need to know is where to practice SQL online. Keep reading for our top picks.

The Best Places to Practice SQL Online

A well-designed and interactive learning environment is essential for understanding the art of properly managing and accessing databases. Whether you're a newbie aiming to grasp the basics or an experienced practitioner looking to enhance your skills, digital platforms have emerged as indispensable tools for advancing your SQL knowledge. Let's dive into the best digital platforms where you can practice SQL and enhance your expertise:

If you're in search of a comprehensive SQL learning platform, look no further than LearnSQL.com. Its distinctive features, rich exercise sets, and challenging content make it stand out to beginners and advanced learners alike.

Let's delve into some of LearnSQL.com's key features to understand how it can benefit your SQL learning journey:

• Interactive Learning: com offers an interactive environment for building and evaluating your SQL skills. You write and run SQL queries directly in the platform, gaining practical experience with each exercise. Plus, there's no need for installation; all you need is an internet connection and a web browser.
• Real-World Examples: com exercises imitate real-world circumstances, guaranteeing that you not only master theory but also practical SQL applications. This makes your abilities extremely relevant in the business world. Having this SQL knowledge can help you strengthen your resume and stand out as a contender for future employment. Check out our article How SQL Skills Can Boost Your Resume for more on this.
• Immediate Feedback: Immediate feedback is critical for learning. LearnSQL.com provides immediate feedback on your SQL queries, assisting you in identifying and correcting problems as you advance through the material.
• Diverse Challenges: com offers a wide range of SQL topics, from simple queries to more advanced concepts like subqueries and window functions . You can choose exercises that match your skill level and learning objectives.

Are you interested in starting a course with LearnSQL.com? Let's take a brief look at some of the courses available:

• SQL Basics : This course is tailored for beginners and covers fundamental SQL concepts. With over 129 interactive exercises, you'll quickly learn to retrieve data from databases and create simple reports.

• SQL Practice Set : Ideal as the next step after completing SQL Basics, this course offers hands-on practice to refine your SQL skills. It covers a range of topics, from simple SELECT statements to more advanced problems. By taking this course, you will strengthen your SQL knowledge and enhance your ability to work with real-world scenarios. Check out Why Is the SQL Practice Set My Favorite Online Course? for an in-depth review.
• Basic SQL Practice: A Store : LearnSQL.com also offers courses tailored to specific business contexts. If you're a store owner looking to leverage SQL, this course is designed for you. With 169 interactive exercises, you can harness the power of databases for your business growth.
• Monthly SQL Practice : Every month, we publish SQL challenges to help keep your skills sharp. In this track, you can review your SQL knowledge every month by completing up to 10 exercises of varying levels of difficulty.

LearnSQL.com is more than just a platform for learning SQL; it's a comprehensive hub where theory meets practice. With its interactive exercises, real-world scenarios, and diverse course offerings, it's the ultimate destination for SQL education and application. That’s why over 96% of LearnSQL.com users would recommend our courses to their colleagues.

Are you curious about how our courses are created? The process starts with an idea – frequently inspired by user feedback or current industry trends. Here's a quick look Behind the Scenes at LearnSQL.com: How Our SQL Courses Are Made . We continuously update and improve based on user feedback, ensuring that every LearnSQL.com course is of the highest quality.

LeetCode is well-known for its coding challenges, which are primarily focused on programming and data science. While it's not exclusively dedicated to SQL practice, it offers a substantial collection of SQL problems of varying difficulty. These tasks are excellent for testing your skills and problem-solving abilities.

This platform is particularly valuable for technical interview preparation, as many tech companies use LeetCode to assess SQL proficiency. It also provides a supportive community for learning SQL.

HackerRank is a versatile platform covering various programming languages, including SQL. It offers SQL challenges suitable for beginning, intermediate, and experienced practitioners.

You can tackle SQL problems and receive instant feedback to enhance your skills. HackerRank's extensive library of SQL challenges is a valuable resource for those aiming to excel in SQL.

SQLZoo is a free online tutorial website that is beginner-friendly and covers the basics of SQL. It offers a structured learning path that covers SQL concepts step by step, from basic queries to advanced topics. Exercises are also included, allowing you to get effective SQL practice.

Each of these platforms has its own advantages and caters to different learning styles. Whether you are looking for competitive challenges, structured courses, or a combination of the two, these sites offer significant opportunities to improve your SQL abilities.

Need more guidance? Here are some resources to assist with your SQL learning journey:

• Top 6 Online SQL Courses for Beginners
• Best PostgreSQL Courses for Beginners
• Best MySQL Courses for Beginners
• SQL Courses for Software Testers
• The Best Apps to Learn SQL

It’s time to practice! While theory serves as the foundation, practice is the fuel that propels you toward SQL mastery. With each query, you develop not only knowledge but also the confidence and abilities required in today's data-driven environment.

The good news is that you are not alone on this path. Online platforms provide a multitude of courses and tools to help you advance faster. These tools offer interactive exercises, real-world scenarios, and the chance to engage with SQL in ways that are directly applicable to your chosen business.

So, why wait? With a single click, you can begin your journey to becoming a SQL expert. Seize the opportunity, choose a course , and let SQL open the door to limitless opportunities for you and your career.

Want to go all in? The All Forever SQL Package at LearnSQL.com is your ultimate gateway to mastering SQL. It gives you lifetime access to all courses and tracks in all SQL dialects available on the LearnSQL.com platform – it currently includes over 70 interactive SQL courses. But it doesn’t end there. You also get access to all courses we release in the future. This truly is the best deal!

So, what are you waiting for? You know where to practice SQL – it’s time to fast-track developing your skills!

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

• Python Multiline String
• Python Multiline Comment
• Python Iterate String
• Python Dictionary
• Python Lists
• Python List Contains
• Page Object Model
• TestNG Annotations
• Python Function Quiz
• Python String Quiz
• Python OOP Test
• Java Spring Test
• Java Collection Quiz
• JavaScript Skill Test
• Selenium Skill Test
• Selenium Python Quiz
• Shell Scripting Test
• Latest Python Q&A
• CSharp Coding Q&A
• SQL Query Question
• Top Selenium Q&A
• Top QA Questions
• Latest Testing Q&A
• REST API Questions
• Linux Interview Q&A
• Shell Script Questions
• Python Quizzes
• Testing Quiz
• Shell Script Quiz
• WebDev Interview
• Python Basic
• Python Examples
• Python Advanced
• Python Selenium
• General Tech

50 SQL Practice Questions for Good Results in Interview

Hello friends, we’ve brought you 50 SQL query interview questions and answers for practice. Solving practice questions is the fastest way to learn any subject. That’s why we’ve selected a set of 50 SQL queries that you can use to step up your learning.

You can start by running the readymade SQL scripts to create the test data . The script includes a sample Worker table, a Bonus, and a Title table with pre-filled data. Just run the SQL script, and you are all set to get started with the SQL queries. Practice with these SQL queries frequently asked in interviews and get your dream jobs in top IT MNCs like Amazon, Flipkart, Facebook, etc.

SQL Query Questions and Answers for Practice

We recommend you go through the questions and form queries by yourself. Try to find answers on your own. However, you can’t start until the required sample data is not in place. We have provided an SQL script to seed the test data. Use it first to create a test database and tables.

By the way, we have many other posts available for SQL interview preparation. So if you are interested, then follow the link given below.

• Most Frequently Asked SQL Interview Questions

Prepare Sample Data To Practice SQL Skills

Sample table – worker, sample table – bonus, sample table – title.

To prepare the sample data, run the following queries in your database query executor or SQL command line. We’ve tested them with the latest version of MySQL Server and MySQL Workbench query browser. You can download these tools and install them to execute the SQL queries. However, these queries will run fine in any online MySQL compiler, you may use them.

SQL Script to Seed Sample Data.

Once the above SQL runs, you’ll see a result similar to the one attached below.

Start with 20 Basic SQL Questions for Practice

Below are some of the most commonly asked SQL queries in interviews from various fields. Get a timer to track your progress and start practicing.

Q-1. Write an SQL query to fetch “FIRST_NAME” from the Worker table using the alias name <WORKER_NAME>.

The required query is:

Q-2. Write an SQL query to fetch “FIRST_NAME” from the Worker table in upper case.

Q-3. write an sql query to fetch unique values of department from the worker table., q-4. write an sql query to print the first three characters of  first_name from the worker table., q-5. write an sql query to find the position of the alphabet (‘a’) in the first name column ‘amitabh’ from the worker table..

• The INSTR does a case-insensitive search.
• Using the BINARY operator will make INSTR work as the case-sensitive function.

Q-6. Write an SQL query to print the FIRST_NAME from the Worker table after removing white spaces from the right side.

Q-7. write an sql query to print the department from the worker table after removing white spaces from the left side., q-8. write an sql query that fetches the unique values of department from the worker table and prints its length., q-9. write an sql query to print the first_name from the worker table after replacing ‘a’ with ‘a’., q-10. write an sql query to print the first_name and last_name from the worker table into a single column complete_name. a space char should separate them., q-11. write an sql query to print all worker details from the worker table order by first_name ascending., q-12. write an sql query to print all worker details from the worker table order by first_name ascending and department descending., q-13. write an sql query to print details for workers with the first names “vipul” and “satish” from the worker table., q-14. write an sql query to print details of workers excluding first names, “vipul” and “satish” from the worker table., q-15. write an sql query to print details of workers with department name as “admin”., q-16. write an sql query to print details of the workers whose first_name contains ‘a’., q-17. write an sql query to print details of the workers whose first_name ends with ‘a’., q-18. write an sql query to print details of the workers whose first_name ends with ‘h’ and contains six alphabets., q-19. write an sql query to print details of the workers whose salary lies between 100000 and 500000., q-20. write an sql query to print details of the workers who joined in feb 2021., 12 medium sql query interview questions / answers for practice.

At this point, you have acquired a good understanding of the basics of SQL, let’s move on to some more intermediate-level SQL query interview questions. These questions will require us to use more advanced SQL syntax and concepts, such as GROUP BY, HAVING, and ORDER BY.

Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.

Q-22. write an sql query to fetch worker names with salaries >= 50000 and <= 100000., q-23. write an sql query to fetch the number of workers for each department in descending order., q-24. write an sql query to print details of the workers who are also managers., q-25. write an sql query to fetch duplicate records having matching data in some fields of a table., q-26. write an sql query to show only odd rows from a table., q-27. write an sql query to show only even rows from a table., q-28. write an sql query to clone a new table from another table..

The general query to clone a table with data is:

The general way to clone a table without information is:

An alternate way to clone a table (for MySQL) without data is:

Q-29. Write an SQL query to fetch intersecting records of two tables.

Q-30. write an sql query to show records from one table that another table does not have., q-31. write an sql query to show the current date and time..

The following MySQL query returns the current date:

Whereas the following MySQL query returns the current date and time:

Here is a SQL Server query that returns the current date and time:

Find this Oracle query that also returns the current date and time:

Q-32. Write an SQL query to show the top n (say 10) records of a table.

MySQL query to return the top n records using the LIMIT method:

SQL Server query to return the top n records using the TOP command:

Oracle query to return the top n records with the help of ROWNUM:

18 Complex SQL Queries for Practice

Now, that you have built a solid foundation in intermediate SQL, It’s time to practice with some advanced SQL query questions. These questions involve queries with more complex SQL syntax and concepts, such as nested queries, joins, unions, and intersects.

Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

Q-34. Write an SQL query to determine the 5th highest salary without using the TOP or limit method.

The following query is using the correlated subquery to return the 5th highest salary:

Use the following generic method to find the nth highest salary without using TOP or limit.

Q-35. Write an SQL query to fetch the list of employees with the same salary.

Q-36. write an sql query to show the second-highest salary from a table., q-37. write an sql query to show one row twice in the results from a table., q-38. write an sql query to fetch intersecting records of two tables., q-39. write an sql query to fetch the first 50% of records from a table..

Practicing SQL query interview questions is a great way to improve your understanding of the language and become more proficient in SQL. In addition to improving your technical skills, practicing SQL query questions can help you advance your career. Many employers seek candidates with strong SQL skills, so demonstrating your proficiency can get you a competitive edge.

Q-40. Write an SQL query to fetch the departments that have less than five people in them.

Q-41. write an sql query to show all departments along with the number of people in there..

The following query returns the expected result:

Q-42. Write an SQL query to show the last record from a table.

The following query will return the last record from the Worker table:

Q-43. Write an SQL query to fetch the first row of a table.

Q-44. write an sql query to fetch the last five records from a table., q-45. write an sql query to print the names of employees having the highest salary in each department., q-46. write an sql query to fetch three max salaries from a table., q-47. write an sql query to fetch three min salaries from a table., q-48. write an sql query to fetch nth max salaries from a table., q-49. write an sql query to fetch departments along with the total salaries paid for each of them., q-50. write an sql query to fetch the names of workers who earn the highest salary..

We hope you enjoyed solving the SQL exercises and learned something new. Stay tuned for our next post, where we’ll bring you even more challenging SQL query interview questions to sharpen your proficiency.

Thanks for reading! We hope you found this tutorial helpful. If you did, please consider sharing it on Facebook / Twitter with your friends and colleagues. You can also follow us on our social media platforms for more resources. if you seek more information on this topic, check out the “You Might Also Like” section below.

SQL Performance Interview Guide

Check 25 SQL performance-related questions and answers.

Enjoy Learning SQL, Team TechBeamers.

You Might Also Like

How to use union in sql queries, if statement in sql queries: a quick guide, where clause in sql: a practical guide, a beginner’s guide to sql joins, 20 sql tips and tricks for performance, sign up for daily newsletter, be keep up get the latest breaking news delivered straight to your inbox..

Popular Tutorials

7 Sites to Practice Selenium for Free in 2024

SQL Exercises – Complex Queries

15 Java Coding Questions for Testers

30 Python Programming Questions On List, Tuple, and Dictionary

• SQL Server training
• Write for us!

Learn to write basic SQL Queries

Essentially, SQL language allows us to retrieve and manipulate data on the data tables. In this article, we will understand and gain the ability to write fundamental SQL queries. At first, we will take a glance at the main notions that we need to know about in order to write database queries.

What is the T-SQL?

SQL is the abbreviation of the Structured Query Language words and, it is used to query the databases. Transact-SQL (T-SQL) language is an extended implementation of the SQL for the Microsoft SQL Server. In this article, we will use the T-SQL standards in the examples.

What is a Relational Database?

Most simply, we can define the relational database as the logical structure in which data tables are kept that can relate to each other.

What is a Data Table?

A  table is a database object that allows us to keep data through columns and rows. We can say that data tables are the main objects of the databases because they are holding the data in the relational databases.

Assume that we have a table that holds the history class students’ details data. It is formed in the following columns.

Name: Student name

SurName: Student surname

Lesson: Opted lesson

Age: Student age

PassMark: Passing mark

We will use this table in our demonstrations in this article. The name of this data table is Student.

Our First Query: SELECT Statement

The SELECT statement can be described as the starting or the zero point of the SQL queries. The SELECT statement is used to retrieve data from the data tables. In the SELECT statement syntax, at first, we specify the column names and separate them with a comma if we use a single column we don’t use any comma in the SELECT statements. In the second step, we write the FROM clause and as a last, we specify the table name. When we consider the below example, it retrieves data from Name and Surname columns, the SELECT statement syntax will be as below:

If we want to retrieve data from only the Name column, the SELECT statement syntax will be as below:

Tip: We can easily try all these examples in this article by ourselves in the SQL Fiddle over this link . After navigating to the link, we need to clear the query panel and execute the sample queries.

The asterisk ( * ) sign defines all columns of the table. If we consider the below example, the SELECT statement returns all columns of the Student table.

• Our main purpose should be to get results from the SQL queries as soon as possible with the least resource consumption and minimum execution time. As possible, we have to avoid using the asterisk (*) sign in the SELECT statements. This usage type causes the consume more IO, CPU and Network cost. As a result, if we don’t need all columns of the table in our queries we can abandon to use asterisk sign and only use the necessary columns

Filtering the Data: WHERE Clause

WHERE clause is used to filter the data according to specified conditions. After the WHERE clause, we must define the filtering condition. The following example retrieves the students whose age is bigger and equal to 20.

LIKE operator is a logical operator that provides to apply a special filtering pattern to WHERE condition in SQL queries. Percentage sign ( % ) is the main wildcard to use as a conjunction with the LIKE operator. Through the following query, we will retrieve the students whose names start with J character.

IN operator enables us to apply multiple value filters to WHERE clause. The following query fetches the students’ data who have taken the Roman and European History lessons.

The BETWEEN operator filters the data that falls into the defined begin and end value. The following query returns data for the students whose marks are equal to and bigger than 40 and smaller and equal to 60.

Sorting the Data: ORDER BY Statement

ORDER BY statement helps us to sort the data according to the specified column. The result set of the data can be sorted either ascending or descending. ASC keyword sorts the data in ascending order and the DESC keyword sorts the data in descending order. The following query sorts the students’ data in descending order according to the PassMark column expressions.

By default ORDER BY statement sorts data in ascending order. The following example demonstrates the default usage of the ORDER BY statement.

Eliminating the Duplicate Data: DISTINCT Clause

The DISTINCT clause is used to eliminate duplicate data from the specified columns so the result set is populated only with the distinct (different) values. In the following example, we will retrieve Lesson column data, however, while doing so, we will retrieve only distinct values with the help of the DISTINCT clause

As we can see, the DISTINCT clause has removed the multiple values and these values added to the result set only once.

In this section, we can test our learnings.

Question – 1:

Write a query that shows student name and surname whose ages are between 22 and 24.

Question – 2:

Write a query that shows student names and ages in the descending order who takes Roman and Ancient History lessons.

In this article, we learned how we can write the basic SQL queries, besides that we demonstrated usage of the queries with straightforward examples.

• Recent Posts

• SQL Performance Tuning tips for newbies - April 15, 2024
• SQL Unit Testing reference guide for beginners - August 11, 2023
• SQL Cheat Sheet for Newbies - February 21, 2023

Related posts:

• SQL Order by Clause overview and examples
• Calculate SQL Percentile using the PERCENT_RANK function in SQL Server
• Top SQL Server Books
• Temporal Table applications in SQL Data Warehouse environments
• Managing untrusted foreign keys

Assignment 1: SQL Queries

Objectives:.

To understand and be able to write simple SQL queries.

Reading Assignments:

6.1 - 6.6, 7.1

Number of points:

Tools for the assignment.

For this assignment, you will use your SQL Server account on IISQLSRV. You should have an account, and you know your password from the first lecture.

As an alternative to SQL Server, you are allowed to use a different database, e.g. MySQL, Postgres, or SQL Server installed in your own machine. In that case you fist need to import the movie database in your database system: you can obtain the data from here . There is no support for database systems other than IISQLSRV.

What to turn in:

Note: a minor modification is made for the turn-in format as follows. please check  thanks for your cooperation :-)     , homework description.

• ACTOR ( id , fname, lname, gender)
• MOVIE ( id , name, year, rank)
• DIRECTOR ( id , fname, lname)
• CAST (pid, mid, role)
• MOVIE_DIRECTOR (did, mid)
• (8') a. List all the actors who acted in at least one film in 2nd half of the 19th century and in at least one film in the 1st half of the 20th century b. List all the directors who directed a film in a leap year  
• (8') List all the movies that have the same year as the movie 'Shrek (2001)', but a better rank. (Note: bigger value of rank implies a better rank)  
• (8') List first name and last name of all the actors who played in the movie 'Officer 444 (1926)'  
• (8') List all directors in descending order of the number of films they directed  
• (8')  a. Find the film(s) with the largest cast.   b. Find the film(s) with the smallest cast. In both cases, also return the size of the cast.  
• (8') Find all the actors who acted in films by at least 10 distinct directors ( i.e. actors who worked with at least 10 distinct directors).  
• (8') Find all actors who acted only in films before 1960.  
• (8') Find the films with more women actors than men.  
• (8') For every pair of male and female actors that appear together in some film, find the total number of films in which they appear together. Sort the answers in decreasing order of the total number of films.  
• (8') For every actor, list the films he/she appeared in their debut year. Sort the results by last name of the actor.  
• (10') The Bacon number of an actor is the length of the shortest path between the actor and Kevin Bacon in the "co-acting" graph. That is, Kevin Bacon has Bacon number 0; all actors who acted in the same film as KB have Bacon number 1; all actors who acted in the same film as some actor with Bacon number 1 have Bacon number 2, etc. Return all actors whose Bacon number is 2. Bonus: Suppose you write a single SELECT-FROM-WHERE SQL query that returns all actors whose Bacon number is infinity. How big is the query? Note: The above "Bonus" problem is ill-stated. The correct one should be as follows: Suppose you write a single SELECT-FROM-WHERE SQL query that returns all actors who have finite Bacon numbers. How big is the query?  
• (10') A decade is a sequence of 10 consecutive years. For example 1965, 1966, ..., 1974 is a decade, and so is 1967, 1968, ..., 1976. Find the decade with the largest number of films.  
• Bonus: Rank the actors based on their popularity, and compute a list of all actors in descending order of their popularity ranks.  You need to come up with your own metric for computing the popularity ranking.  This may include information such as the number of movies that an actor has acted in; the 'popularity' of these movies' directors (where the directors' popularity is the number of movies they have directed), etc.  Be creative in how you choose your criteria of computing the actors' popularity.   For this answer, in addition to the query, also turn in the criteria you used to rank the actors.

ArtOfTesting

Interview , SQL

Top 50 SQL Query Interview Questions and Answers for Practice

Last updated on April 17, 2024

Hello friends! in this post, we will see some of the most common SQL queries asked in interviews. Whether you are a DBA, developer, tester, or data analyst, these SQL query interview questions and answers are going to help you. In fact, I have been asked most of these questions during interviews in the different phases of my career.

If you want to skip the basic questions and start with some tricky SQL queries then you can directly move to our SQL queries interview questions for the experienced section. 

Consider the below two tables for reference while trying to solve the SQL queries for practice .

Table – EmployeeDetails

Table – EmployeeSalary

For your convenience, I have compiled the top 10 questions for you. You can try solving these questions and click on the links to go to their respective answers.

• SQL Query to fetch records that are present in one table but not in another table.
• SQL query to fetch all the employees who are not working on any project.
• SQL query to fetch all the Employees from EmployeeDetails who joined in the Year 2020.
• Fetch all employees from EmployeeDetails who have a salary record in EmployeeSalary.
• Write an SQL query to fetch a project-wise count of employees.
• Fetch employee names and salaries even if the salary value is not present for the employee.
• Write an SQL query to fetch all the Employees who are also managers.
• Write an SQL query to fetch duplicate records from EmployeeDetails.
• Write an SQL query to fetch only odd rows from the table.
• Write a query to find the 3rd highest salary from a table without top or limit keyword.

Or, you can also jump to our below two sections on SQL query interview questions for freshers and experienced professionals.

SQL Query Interview Questions for Freshers

Here is a list of top SQL query interview questions and answers for fresher candidates that will help them in their interviews. In these queries, we will focus on the basic SQL commands only.

1. Write an SQL query to fetch the EmpId and FullName of all the employees working under the Manager with id – ‘986’. We can use the EmployeeDetails table to fetch the employee details with a where clause for the manager-

2. Write an SQL query to fetch the different projects available from the EmployeeSalary table. While referring to the EmployeeSalary table, we can see that this table contains project values corresponding to each employee, or we can say that we will have duplicate project values while selecting Project values from this table. So, we will use the distinct clause to get the unique values of the Project.

3. Write an SQL query to fetch the count of employees working in project ‘P1’. Here, we would be using aggregate function count() with the SQL where clause-

4. Write an SQL query to find the maximum, minimum, and average salary of the employees. We can use the aggregate function of SQL to fetch the max, min, and average values-

5. Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000. Here, we can use the ‘Between’ operator with a where clause.

6. Write an SQL query to fetch those employees who live in Toronto and work under the manager with ManagerId – 321. Since we have to satisfy both the conditions – employees living in ‘Toronto’ and working in Project ‘P2’. So, we will use the AND operator here-

7. Write an SQL query to f etch all the employees who either live in California or work under a manager with ManagerId – 321. This interview question requires us to satisfy either of the conditions – employees living in ‘California’ and working under Manager with ManagerId – 321. So, we will use the OR operator here-

8. Write an SQL query to fetch all those employees who work on Projects other than P1. Here, we can use the NOT operator to fetch the rows which are not satisfying the given condition.

Or using the ‘not equal to’ operator-

For the difference between NOT and <> SQL operators, check this link – Difference between the NOT and != operators .

9. Write an SQL query to display the total salary of each employee adding the Salary with Variable value. Here, we can simply use the ‘+’ operator in SQL.

10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ends with any sequence of characters. For this question, we can create an SQL query using like operator with ‘_’ and ‘%’ wild card characters, where ‘_’ matches a single character and ‘%’ matches ‘0 or multiple characters.

11. Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’. In order to get unique employee ids from both tables, we can use the Union clause which can combine the results of the two SQL queries and return unique rows.

12. Write an SQL query to fetch common records between two tables. SQL Server – Using INTERSECT operator-

MySQL – Since MySQL doesn’t have INTERSECT operator so we can use the subquery-

13. Write an SQL query to fetch records that are present in one table but not in another table. SQL Server – Using MINUS- operator-

MySQL – Since MySQL doesn’t have a MINUS operator so we can use LEFT join-

14. Write an SQL query to fetch the EmpIds that are present in both the tables –  ‘EmployeeDetails’ and ‘EmployeeSalary. Using subquery-

15. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary. Using subquery-

16. Write an SQL query to fetch the employee’s full names and replace the space with ‘-’. Using the ‘Replace’ function-

17. Write an SQL query to fetch the position of a given character(s) in a field. Using the ‘Instr’ function-

18. Write an SQL query to display both the EmpId and ManagerId together. Here we can use the CONCAT command.

19. Write a query to fetch only the first name(string before space) from the FullName column of the EmployeeDetails table. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use the LOCATE method in MySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use the SUBSTRING OR MID method. MySQL – using MID

SQL Server – using SUBSTRING

20. Write an SQL query to uppercase the name of the employee and lowercase the city values. We can use SQL Upper and Lower functions to achieve the intended results.

21. Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field. Here, we can use the ‘Length’ function. We can subtract the total length of the FullName field from the length of the FullName after replacing the character – ‘n’.

22. Write an SQL query to update the employee names by removing leading and trailing spaces. Using the ‘Update’ command with the ‘LTRIM’ and ‘RTRIM’ functions.

23. Fetch all the employees who are not working on any project. This is one of the very basic interview questions in which the interviewer wants to see if the person knows about the commonly used – Is NULL operator.

24. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as a subquery to find the fullName of the employee from the EmployeeDetails table.

25. Write an SQL query to find the current date-time. MySQL-

SQL Server-

26. Write an SQL query to fetch all the Employee details from the EmployeeDetails table who joined in the Year 2020. Using BETWEEN for the date range ’01-01-2020′ AND ’31-12-2020′-

Also, we can extract the year part from the joining date (using YEAR in MySQL)-

27. Write an SQL query to fetch all employee records from the EmployeeDetails table who have a salary record in the EmployeeSalary table. Using ‘Exists’-

28. Write an SQL query to fetch the project-wise count of employees sorted by project’s count in descending order. The query has two requirements – first to fetch the project-wise count and then to sort the result by that count. For project-wise count, we will be using the GROUP BY clause and for sorting, we will use the ORDER BY clause on the alias of the project count.

29. Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee. This is again one of the very common interview questions in which the interviewer just wants to check the basic knowledge of SQL JOINS. Here, we can use the left join with the EmployeeDetail table on the left side of the EmployeeSalary table.

30. Write an SQL query to join 3 tables. Considering 3 tables TableA, TableB, and TableC, we can use 2 joins clauses like below-

For more questions on SQL Joins, you can also check our top SQL Joins Interview Questions .

SQL Query Interview Questions for Experienced

Here is a list of some of the most frequently asked SQL query interview questions for experienced professionals. These questions cover SQL queries on advanced SQL JOIN concepts, fetching duplicate rows, odd and even rows, nth highest salary, etc.

31. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table. Here, we have to use Self-Join as the requirement wants us to analyze the EmployeeDetails table as two tables. We will use different aliases ‘E’ and ‘M’ for the same EmployeeDetails table.

32. Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId). In order to find duplicate records from the table, we can use GROUP BY on all the fields and then use the HAVING clause to return only those fields whose count is greater than 1 i.e. the rows having duplicate records.

33. Write an SQL query to remove duplicates from a table without using a temporary table. Here, we can use delete with alias and inner join. We will check for the equality of all the matching records and then remove the row with a higher EmpId.

34. Write an SQL query to fetch only odd rows from the table. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-

In case we don’t have such a field then we can use the below queries. Using Row_number in SQL server and checking that the remainder when divided by 2 is 1-

Using a user-defined variable in MySQL-

35. Write an SQL query to fetch only even rows from the table. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-

In case we don’t have such a field then we can use the below queries. Using Row_number in SQL server and checking that the remainder, when divided by 2, is 1-

36. Write an SQL query to create a new table with data and structure copied from another table .

37. Write an SQL query to create an empty table with the same structure as some other table. Here, we can use the same query as above with the False ‘WHERE’ condition-

38. Write an SQL query to fetch top n records. In MySQL using LIMIT-

In SQL server using TOP command-

39. Write an SQL query to find the nth highest salary from a table. Using Top keyword (SQL Server)-

Using limit clause(MySQL)-

40. Write SQL query to find the 3rd highest salary from a table without using the TOP/limit keyword. This is one of the most commonly asked interview questions. For this, we will use a correlated subquery. In order to find the 3rd highest salary, we will find the salary value until the inner query returns a count of 2 rows having a salary greater than other distinct salaries.

For the nth highest salary-

Scenario-based SQL Query Interview Questions

Let’s see some interview questions based on different scenarios. The questions are of varying difficulty levels and the goal is to prepare you for different real-time scenario-based questions.

41. Consider a SalesData with columns SaleID , ProductID , RegionID , SaleAmount . Write a query to find the total sales amount for each product in each region. The below query sums up SaleAmount for each combination of ProductID and RegionID , giving an insight into the total sales per product per region.

42. Write a query to find employees who earn more than their managers. Here, we will write a query that joins the EmployeeDetails table with itself to compare the salaries of employees with their respective managers.

43. Consider a BookCheckout table with columns – CheckoutID , MemberID , BookID , CheckoutDate , ReturnDate . Write an SQL query to find the number of books checked out by each member.

44. Consider a StudentGrades table with columns – StudentID , CourseID , Grade . Write a query to find students who have scored an ‘A’ in more than three courses. Here we will write an SQL query that filters students who have received an ‘A’ grade and groups them by StudentID , counting the number of ‘A’ grades per student.

45. Consider a table OrderDetails with columns – OrderID , CustomerID , ProductID , OrderDate , Quantity , Price . Write a query to find the average order value for each customer. The below query calculates the average order value (quantity multiplied by price) for each customer.

46. Consider a table PatientVisits with Columns VisitID , PatientID , DoctorID , VisitDate , Diagnosis . Write a query to find the latest visit date for each patient.

47. For a table FlightBookings with columns – BookingID , FlightID , PassengerID , BookingDate , TravelDate , Class , write a query to count the number of bookings for each flight class. Here, we will write an SQL query that groups the bookings by Class and counts the number of bookings in each class.

48. Consider a table FoodOrders with columns – OrderID , TableID , MenuItemID , OrderTime , Quantity . Write a query to find the most ordered menu item. For the desired output, we will group the orders by MenuItemID and then sort the results by the count in descending order, fetching the top result.

49. Consider a table Transactions with columns – TransactionID , CustomerID , ProductID , TransactionDate , Amount . Write a query to find the total transaction amount for each month. The below query sums the Amount for each month, giving a monthly total transaction amount.

50. Consider a table EmployeeAttendance with columns – AttendanceID , EmployeeID , Date , Status . Write a query to find employees with more than 5 absences in a month. This query filters the records for absent status, groups them by EmployeeID and month, and counts absences, filtering for more than 5 absences.

This concludes our post on frequently asked SQL query interview questions and answers . I hope you practice these questions and ace your database interviews. If you feel, we have missed any of the common interview questions on SQL then do let us know in the comments and we will add those questions to our list.

Do check our article on –  RDBMS Interview Questions , focussing on the theoretical interview questions based on the DBMS and SQL concepts.

JMeter Interview Questions

Top rdbms interview questions | dbms viva questions, 70 thoughts on “top 50 sql query interview questions and answers for practice”.

Thank You so much, these queries are very useful.

select max(salary) as maxsalary from emp where salary ( select max(salary) as maxsalary from emp where salary( select max(salary) as maxsalary from emp))

you must have to give the condition in every where clause for getting result

Thanks for the queries.

Hi Geet, thanks for pointing out, please let us know, what you find wrong in this query.

can I write the query for 6th question like

Select FullName from EmployeeDeatils where ManagerId is not null;

Because if anyone has ManagerId then he must be a manager right.so need to join??

Hi Ramesh, the ManagerId field in the EmployeeDetails table refers to the Manager of that Employee. So, your query will return only the Employees with Manager assigned.

how to fetch last second row records ???? and anyone sugest me the most asking sql queries in mnc

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Table1 ORDER BY RowID DESC) X ORDER BY RowID

Oracle 12c and above:

select * from DEPARTMENTS offset (select count(*) -2 from departments) rows fetch next 1 rows only;

In Oracle SQL server:- Select b.*,nth_value(id,2) over(order by Id desc range between unbounded preceding and unbounded following) from your_table b;

with cte as ( select *,ROW_NUMBER() over(order by id desc) as rownum

from Employee1 ) select * from cte where rownum =2

select * from Employee

Movie table Creat table ” movie ” { “id” int(110 not null default ‘0’, “name” varchar(100)default null, “year” int(11) default null, ” rank” flaot default null, primary key (” id”)

Considering the above table how many movies does the dataset have for the year 1982 ! write ur query below

please help me to solve the query ….

SELECT COUNT(*) FROM movie WHERE year = 1982

select * from table order by ID(primary key) desc limit 1,1;

In Question no. 39 Put N=2 and ORDER BY asc You will get second lowest salary

select * from table name order by column name offset (select count(*) from table name)-1 rows fetch next 1 rows only

with ctek as ( select*row_number() over(order by id desc) as rowno from emp )

select*from ctek

where rowno=2

select*from emp

select * from EmployeeDeatils order by EMPID DESC LIMIT 1,1;

Thanks a lot for sharing these SQL queries. I have my interview tomorrow, these questions will really help.

Appreciate your feedback Amit :-). Best of luck with your interview.

Hi Kuldeep, Appreciate your efforts…

In Que.12 and Que. 13 , you have unconsciously written table name ‘ManagerSalary’ instead of EmployeeDetails.

Rest it is good blend of list of questions.

Hi Mithilesh,

Thanks. Actually, I have intentionally used a new table ManagerSalary. Since I have used ‘*’ in the queries which would require a similar table structure. Hence I introduced a new table – ManagerSalary, assuming the table to have a similar structure like that of EmployeeSalary.

This is extremely confusing and doesn’t make much sense. Some language about creating a new table would have been helpful. I kept searching this page for a ManagerSalary table.

This confused me too

This is really good for beginners.

Hi Kuldeep,

Great and fun article! A word of warning about the first answer to question 26. Not sure about SQL Server, but definitely in Oracle using BETWEEN with dates could get you in trouble. In “BETWEEN ‘2020/01/01’AND ‘2020/12/31′”, those dates are interpreted with a timestamp of 00:00. So with the latter date, it’ll only retrieve those hired until 2020/12/30 (or 2020/12/31 at exactly 00:00), and it would miss anyone hired after 00:00 on 2020/12/31 (so a person hired at 09:00 on 12/31 would be missed. Plus there are lots of additional complexities with doing something like BETWEEN ‘2020/01/01’AND ‘2020/12/31 23:59:59’. So in my experience, to get everyone hired in 2020, you’re better off using: AND dateOfJoining >= ‘2020/01/01’ AND dateOfJoining < '2021/01/01' …or just use the extract function like in your second answer 🙂

Thanks a lot, Phil.

Thanks for it

getting records of one managerid under their employeeid details alone have to come like their group memeber alone

In the following query can you please explain this line

WHERE Emp2.Salary > Emp1.Salary

SELECT Salary FROM EmployeeSalary Emp1 WHERE 2 = ( SELECT COUNT( DISTINCT ( Emp2.Salary ) ) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary )

This is a use of Correlated Subquery. First, You are interested in fetching the “salary” from the EmployeeSalary Table or Emp1; You give the condition or criteria in WHERE clause.

The condition returns a value, where the total count of unique observations from Table EmployeeSalary. You are conditioning with respect to the same table.

For nth highest salary- SQL Server

SELECT Salary FROM EmployeeSalary Emp1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( Emp2.Salary ) ) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary )

Getting Error : Invalid column name ‘n’. Please tell me Some One ?

You don’t have to directly use ‘n’. You need to replace ‘n’ with a number e.g. if you want to find the 3rd highest salary, n would be 3.

This is be a more simple solution.

For nth highest salary,

Select Salary From EmployeeSalary Order By `Salary` Desc limit n-1,1;

For example, if we need 3rd highest salary, query will be

Select Salary From EmployeeSalary Order By `Salary` Desc limit 2,1;

But by this query, list will be printed from 3rd to minimum. Right ?

select max Salary from (select distinct Salary from EmployeeSalary order by Salary desc) where rownum < n+1;

thanks for sharing

Hi Kuldeep, Really useful and on point article, Great help at interviews. Thanks👍🏻

Hi, Can you please provide me the Create query for the above table and also provide me the create a query of ManagerSalary with insert data.

Hi kuldeep, Thanks for the effort you put in creating this blog. It’s very nice.

I just want to add one more sql query problem which i was asked in my Oracle Interview.

Suppose there is a table with 3 attr : city 1 city2 Distance Hyd. goa. 500 goa. Hyd. 500

These tuples represent the same information , so write an SQL query to remove these type of duplicates.

Ans : delete from t where (city1,city2) in ((select t1.city1,t1.city2 from t t1 where exists(select t2.city2 from t t2 where t2.city2=t1.city1) and exists(select t2.city1 from t t2 where t2.city1=t1.city2)) minus (select t1.city1,t1.city2 from t t1 where exists(select t2.city2 from t t2 where t2.city2=t1.city1) and exists(select t2.city1 from t t2 where t2.city1=t1.city2) fetch first 1 rows only));

Thanks a lot, Rahul. These types of questions will definitely help other readers. Keep contributing :-).

Cant we solve this using self join?

Delete E1 from E1.tab Join E2.tab where E1.city1 = E2.city2 and E1.city2 = E2.city 1 and E1.cost = E2.cost;

DELETE FROM distance t1 WHERE EXISTS ( SELECT 1 FROM distance t2 WHERE t1.city1= t2.city2 AND t1.city2 = t2.city1 AND t1.distance = t2.distance AND t1.rowid > t2.rowid );

Display list of employee having first name David or Diana without using like, in and or operater.please answer

Thanks kuldeep for such a good article and sharing valuable sql questions and answers which will help a lot for interview preparation.Hats off you.

How to fetch emplid with full name second letter is o

select Fullname from employeedetails where Fullname like’_o%’;

select empid, fullname where fullname like ‘_0__%’;

Select emplid, fullname from employee where fullname like ‘_o%’;

hi kuldeep sir, In my pc SQL does not support TOP keyword but it support LIMIT keyword. rest of the queries is nicely understandable.

I am a beginner in SQL and I was asked the below questions in one of my interviews, can anyone help me with the below.

Question1:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount)

write query to fetch 10 transaction made in last month by 1 customer

Question2:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount) product table has 2 columns (product_id, product_name)

write query to list all the product which are never sold write query to list all the product which are most sold

Question3:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount) product table has 2 columns (product_id, product_name)

Write query to fetch customer id, first transaction date, last transaction date, the difference in transaction amount, and difference in transaction date

1. SELECT * FROM ( SELECT transaction_id, customer_id, transaction_date, product_id, transaction_amount, ROW_NUMBER() OVER (ORDER BY transaction_date DESC) AS rn FROM transaction WHERE customer_id = 1 –(let 1) and transaction_date >= TRUNC(SYSDATE, ‘MM’) – 30 ) WHERE rn <= 10 order by transaction_id,customer_id; 2. SELECT p.product_id, p.product_name, total_amount FROM products p JOIN ( SELECT product_id, SUM(transaction_amount) AS total_amount FROM transaction GROUP BY product_id ORDER BY total_amount DESC ) t ON p.product_id = t.product_id WHERE ROWNUM = 1; 3. SELECT p.product_id, p.product_name FROM products p LEFT OUTER JOIN transaction t ON p.product_id = t.product_id WHERE t.transaction_id IS NULL; 4. SELECT t.customer_id, MIN(t.transaction_date) AS first_transaction_date, MAX(t.transaction_date) AS last_transaction_date, MAX(t.transaction_amount) – MIN(t.transaction_amount) AS diff_amount, MAX(t.transaction_date) – MIN(t.transaction_date) AS diff_date FROM transaction t GROUP BY t.customer_id;

Very helpful queries. 🙂

Really helpful article. Thank you

great art of testing. thanks for all the effort.

Thank you for sharing your knowledge with us.

select * from table_name where first_name=’David’ or first_name=’Diana’;

In the 31st question, can I write the query as SELECT DISTINCT(Fullname) FROM Employeedetails WHERE EmpID IN ( SELECT DISTINCT(ManagerID) FROM Employeedetails);

SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘David’ UNION SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘Diana’;

Hi, I have asked a question in My interview I have an order table with the following columns orderID,customerID,orderDate, sales amount. Please write a query to show total sales amount by order date? For these question we can use joins right?

select sales,orderdate,sum(sales) as total sales from order group by orderdate,sales

select FullName from EmployeeDetails where ManagerId is not null

SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘David’ UNION SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘Diana’;

Write a query to display details of employs with the text “Not given”, if commission is null?

TABLE ITEM_NAME | COST APPLE | 4 POTATO | 3 ORANGE | 8 TOMATO | 5

What will be the ms sql query to get following output in a table? OUTPUT TABLE ITEM | TOTAL_COST VEGETABLES | 8 FRUITS | 12

SELECT ITEMS, SUM(TOTAL_COST) FROM ( SELECT CASE WHEN Item IN (‘APPLE’,’ORANGE’) then ‘FRUITS’ WHEN Item IN (‘POTATO’,’TOMATO’) then ‘VEGITABLES’ END ITEMS, CASE WHEN Item IN (‘APPLE’,’ORANGE’) then sum (cost) WHEN Item IN (‘POTATO’,’TOMATO’) then sum (cost) END TOTAL_COST FROM gross_market GROUP by Item ) A GROUP BY items

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

MySQL Tutorial

Mysql database, mysql references, mysql examples, mysql exercises.

You can test your MySQL skills with W3Schools' Exercises.

We have gathered a variety of MySQL exercises (with answers) for each MySQL Chapter.

Try to solve an exercise by filling in the missing parts of a code. If you're stuck, hit the "Show Answer" button to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start MySQL Exercises

Start MySQL Exercises ❯

If you don't know MySQL, we suggest that you read our MySQL Tutorial from scratch.

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

• Trending Now
• Foundational Courses
• Data Science
• Practice Problem
• Machine Learning
• System Design
• DevOps Tutorial

Explore the fundamentals of the SQL SELECT query with our comprehensive tutorial. This guide is perfect for database administrators, developers, and anyone interested in mastering data retrieval techniques in SQL.

In this tutorial, you'll learn:

Understanding the SELECT Query: Gain a foundational understanding of the SQL SELECT query, which is used to retrieve data from a database. The SELECT statement is one of the most commonly used SQL commands and is essential for querying and analyzing data.

Basic Syntax:

• The SELECT query allows you to specify which columns to retrieve from a table.
• You can select specific columns, all columns, or use various clauses to filter and manipulate the data returned.

Key Components of the SELECT Query:

• SELECT Clause : Specifies the columns to be retrieved.
• FROM Clause : Specifies the table from which to retrieve the data.
• WHERE Clause : Filters the records based on specified conditions.
• ORDER BY Clause : Sorts the result set in ascending or descending order.
• GROUP BY Clause : Groups rows that have the same values into summary rows.
• HAVING Clause : Filters groups based on specified conditions.
• JOIN Operations : Combines rows from two or more tables based on a related column.

Selecting Specific Columns :

• Retrieve specific columns from a table. For example, you might want to select the name and age columns from an employees table.

Selecting All Columns :

• Retrieve all columns from a table using a wildcard symbol. This is useful when you need all the data from a table.

Using the WHERE Clause :

• Filter records based on specified conditions. For example, you might want to select employees who are older than 30.

Sorting Results with ORDER BY :

• Sort the result set by one or more columns. You might want to sort employees by their age in ascending order.

Grouping Results with GROUP BY :

• Group rows that have the same values into summary rows. For instance, you might want to count the number of employees in each department.

Filtering Groups with HAVING :

• Filter groups based on specified conditions. For example, you might want to select departments that have more than five employees.

Joining Tables :

• Combine rows from two or more tables based on a related column. For instance, you might want to join an employees table with a departments table to get the department name for each employee.

Handling Edge Cases: Tips on managing various edge cases such as:

• Missing Data : Handling NULL values in columns.
• Performance : Optimizing queries for large datasets.
• Complex Joins : Managing complex join operations with multiple tables.

Applications and Real-World Use: Discuss real-world applications of the SELECT query, such as data analysis, reporting, and application development.

By the end of this tutorial, you’ll be well-equipped to use the SQL SELECT query to retrieve and manipulate data efficiently, enhancing your database management skills and your ability to analyze data effectively.

For a comprehensive guide on the SQL SELECT query, including detailed explanations and practical tips, check out our full article at https://www.geeksforgeeks.org/sql-select-query/ .

This tutorial will not only improve your understanding of SQL but also prepare you to implement powerful data retrieval techniques in your database management projects.

Home Posts Topics Members FAQ

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use .

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.