Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data

Product Information

We select the department, use AVG() with the salary column, and group the output by department. Output department You already know how this works. The query is the same as in the previous example, but now it uses the MAX() function. Output department Example 2 - Consider the following dependencies in relation to R(W,X,Y,Z) WX - > Y [W and X together determine Y]

The required query is: Select * from Worker where year(JOINING_DATE) = 2021 and month(JOINING_DATE) = 2; Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’. DROP TABLE command deletes complete data from the table along with removing the complete table structure too. In case our requirement entails just remove the data, then we would need to recreate the table to store data in it. In such cases, it is advised to use the TRUNCATE command. 21. How do you perform case-insensitive searches using regular expressions in PostgreSQL? Let’s not lose any time! We’ll introduce the dataset, and then we’re off to writing and explaining basic SQL queries. Dataset The required query is: Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Amitabh'; SQLTest is an online SQL practice tool that lets you create tables, populate tables, write SQL queries on them and see the results.DELIMITER ; /* Reset the delimiter */ 40. How to create empty tables with the same structure as another table? Another example? You take a painting class at your university. The teacher tells you about Picasso, Salvador Dali, and how to mix paints properly to get an aquamarine color. But until you stand in front of the easel and paint, you'll never be able to say that you are a painter. Maybe an art expert, but not an artist. Where to practice SQL? The purpose of the above query is to find the total salary amount for each department. This is achieved in the following way.

The Cambridge dictionary defines practice as the act of doing something regularly or repeatedly to improve your skill. The matter is simple. Without practice, you will never become an expert in any field. It is an essential component of developing and expanding your skills.

Now comes the ON condition. It is used to specify the columns on which the two tables will be joined. Usually, those are the columns that store the same data in both tables. In other words, we join the tables on the primary and foreign keys. A primary key is a column (or columns) that uniquely defines each row in the table. A foreign key is a column in the second table that refers to the first table. In our example, the column id from the table employees is its primary key. The column employee_id from the table quarterly_sales is the foreign key, as it contains the value of the column id from the first table. Working on this particular course was a great pleasure. Our team has designed interesting tasks and challenges and created a course that is perfect for practicing SQL. Now, that you should have a solid foundation in intermediate SQL, let’s take a look at some more advanced SQL query questions. These questions will require us to use 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. The required query is: Select * from Worker where SALARY between 100000 and 500000; Q-20. Write an SQL query to print details of the Workers who joined in Feb 2021. The required query is: SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);

The required query is: Select * from Worker where WORKER_ID = (SELECT min(WORKER_ID) from Worker); Q-44. Write an SQL query to fetch the last five records from a table. This example will again demonstrate how to filter output using WHERE. It will be a bit more advanced this time, as we’ll use a logical operator. In SQL, logical operators allow you to test if the filtering condition is true or not. They also allow you to set multiple conditions. Now that we have two tables joined, we are free to select any column from both tables. We select id, first_name, and last_name from employees. Then we add each column from the table quarterly_sales showing the quarterly sales and name it total_sales_2022. Each column in SELECT also has the table alias before it, with the alias and the column name separated by a dot. Then we select the column q1_2022 and use the addition arithmetic operator to add the q2_2022 column. We also give this new calculated column an alias of h1_2022 using the AS keyword. Output employee_idLet’s start explaining the query with the FROM clause. This is familiar: to use the data from the table employees, you need to reference it in FROM. We also give this table an alias (‘e’), so that we don’t have to write the table’s full name later on. The required query is: SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 5; Q-41. Write an SQL query to show all departments along with the number of people in there. The AVG() function calculates the average value. You can use this query whenever you want to group data and show the average value for each group. Query