learning java coding: sql

Showing posts with label sql. Show all posts

Sunday, March 29, 2020

How To Purpose Alteration Ascendance Inward Sql - Database Tutorial

In this SQL tutorial we volition run across how to utilisation ALTER ascendency command inwards tabular array on database. ALTER ascendency is mainly used to add, modify as well as driblet columns, indexes as well as constraints on tabular array inwards relational database e.g. MySQL, Oracle, Sybase as well as SQL Server. Though ALTER is non portion of classical CRUD performance only it’s ane of the of import DDL command. One of the most frequent utilisation of ALTER ascendency inwards SQL is adding as well as removing indexes to amend performance of SQL SELECT queries. In this SQL as well as database tutorial nosotros volition run across unlike ways of using ALTER command. By using ALTER ascendency nosotros tin add, edit or modify table, views as well as databases. We tin add together novel column on the table, nosotros tin alter value of column or nosotros tin rename column also.

10 ALTER ascendency Example inwards SQL

we volition run across how to utilisation ALTER ascendency command inwards tabular array on database How to utilisation ALTER COMMAND inwards SQL - database tutorial

Let’s run across around of the most oft used instance of ALTER ascendency inwards SQL. In this department nosotros volition run across How to utilisation ALTER ascendency to add, modify as well as driblet existing columns inwards table.

Example 1 : How to add together column inwards existing tabular array using ALTER clause inwards SQL:

Suppose nosotros convey ane tabular array called pupil alongside champaign roll_number, stu_name, class, subject as well as nosotros desire to add together ane to a greater extent than column called marks-obtained, how volition produce using ALTER Command.

ALTER TABLE Student ADD marks_obtained Number (3);

When this champaign is added to the tabular array it volition comprise NULL Value yesteryear default.

Example two : How to modify existing column using ALTER ascendency inwards SQL:

Suppose straight off nosotros desire to modify inwards a higher house added champaign mark_obtained from divulge to decimal information type nosotros tin utilisation modify column along alongside alter command.

ALTER TABLE Student ALTER COLUMN marks_obtained DECIMAL (5, 2);

Example three : How to driblet existing column from tabular array using ALTER ascendency inwards SQL:

Now if nosotros desire to driblet around existing column from the tabular array nosotros utilisation driblet along alongside ALTER ascendency

ALTER TABLE Student DROP column marks_obtained;

Example 4. How to driblet Primary Key Constraint using ALTER ascendency inwards database :

Suppose at that topographic point is a PRIMARY KEY CONSTRAINT named 'pk_roll_num' for the column roll_number of the Student tabular array as well as nosotros desire to driblet this constraint

ALTER TABLE Student DROP CONSTRAINT 'pk_roll_num’;

Example 5. How to add together primary key constraints using ALTER ascendency inwards SQL:

Now if nosotros desire to add together primary key constraint inwards tabular array Student named 'pk_roll_num' for the column roll_number' . We produce similar this

ALTER TABLE Student ADD Constraint 'pk_roll_num PRIMARY KEY(roll_number)

Example 6. How to driblet unusual key Constraint using Alter command:

Suppose at that topographic point is a unusual key constraint named 'fk_roll_num' for the column roll_number' of the 'Marks' table as well as nosotros desire to driblet this constraint

ALTER TABLE Marks DROP CONSTRAINT 'fk_roll_num’;

Example seven : How to add together unusual key Constraints using alter command

Now if nosotros desire to add together foreign key constraint inwards tabular array Marks named 'fk_roll_num' for the column roll_number' .we produce similar this

ALTER TABLE Marks ADD Constraint 'fk_roll_num FOREIGN KEY (roll_number) REFERENCES Students (roll_number);

Example 8 : How to add together unique key Constraints using ALTER command

Now if nosotros desire to add together Unique key constraint inwards tabular array Student named ‘unique_roll_no ' for the column roll_number' .we produce similar this

ALTER TABLE Student ADD CONSTRAINT unique_roll_no UNIQUE (roll_Number);

Example 9 : How to driblet unique Key Constraint using ALTER command

Now if nosotros desire to driblet unique key constraint inwards tabular array Student named ‘unique_roll_no ' for the column roll_number' .we produce similar this

ALTER TABLE Student DROP CONSTRAINT unique_roll_no);

Example 10 : How to add together depository fiscal establishment fit Constraint using ALTER ascendency inwards SQL

Basic syntax for adding depository fiscal establishment fit constraint inwards tabular array is similar this

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

This is all almost ALTER ascendency examples inwards SQL. We convey seen 10 unlike ways of using ALTER ascendency inwards SQL. ALTER is ane of the most of import patch working inwards whatsoever database e.g. MySQL, Oracle or SQL Server, peculiarly from ascendency line. Since nosotros often require to add, modify as well as driblet columns, indexes as well as constraints inwards table.

Further Learning
Difference betwixt clustered as well as non-clustered index inwards SQL

Difference betwixt View as well as materialized stance inwards SQL

Difference betwixt candidate key as well as primary key inwards SQL

Difference betwixt correlated as well as noncorrelated subquery inwards database

When to utilisation truncate vs delete inwards database

P.S. - If y'all are looking for online training/course to larn SQL from scratch, I advise y'all joining Introduction to SQL yesteryear Jon Flanders. It's ane of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering as well as SQL enquiry optimization.

Saturday, March 28, 2020

Difference Betwixt Self Too Equi Bring Together Inwards Sql - Inner Bring Together Event Mysql

The principal deviation betwixt Self Join together with Equi Join is that In Self Join nosotros bring together ane tabular array to itself rather than joining 2 tables. Both Self Join together with Equi Join are types of INNER Join inwards SQL, but at that spot is the subtle deviation betwixt two. Any INNER Join amongst equal every bit bring together predicate is known every bit Equi Join. SQL Joins are the cardinal concept of SQL similar to correlated together with noncorrelated subqueries or using grouping yesteryear clause together with a proficient agreement of diverse types of SQL bring together is the must for whatever programmer. By the way, If yous convey written INNER bring together using where clause than using comparing operator every bit = volition hold upwards known every bit an equijoin. Equi bring together or Self bring together are non a formal JOIN or component division of the syntax, instead, they are a but pop means to refer sure enough bring together examples.

One of the best instance of Self Join, I convey seen inwards an SQL query Interview questions is "How produce yous notice all Employees who are Managers inwards Employee table", which is usually asked along amongst or together with then other pop inquiry how to notice minute highest salary of employee or questions related to join iii tables inwards ane SQL query.

In this SQL tutorial, nosotros volition larn self-join yesteryear instance piece solving this SQL query.

Self Join Example inwards SQL

In gild to solve this query let's starting fourth dimension run across schema together with information of Employee table.

mysql> select * from employee;
+--------+----------+---------+--------+--------+
| emp_id | emp_name | dept_id | salary | mgr_id |
+--------+----------+---------+--------+--------+
| 103 | Jack | 2 | 1400 | 104 |
| 104 | John | 2 | 1450 | 104 |
| 105 | Johnny | 3 | 1050 | 104 |
| 108 | Alan | 3 | 1150 | 104 |
| 106 | Virat | 4 | 850 | 105 |
| 107 | Vina | 4 | 700 | 105 |
| 109 | joya | 4 | 700 | 105 |
+--------+----------+---------+--------+--------+
7 rows in set (0.00 sec)

In above table all employees who are managers has at that spot emp_id every bit mgr_id inwards other employees together with yesteryear using SELF JOIN i.e. bring together 2 instances of employee tabular array together with comparing, nosotros tin notice all employees who are managers. Here is the SELECT query example using self-join:

The principal deviation betwixt Self Join together with Equi Join is that In Self Join nosotros bring together ane tabl Difference betwixt Self together with Equi Join inwards SQL - INNER Join instance MySQL

In this instance of self join, nosotros convey joined employee tabular array to itself yesteryear using 2 tabular array aliases e together with m. We convey likewise used distinct keyword to take away duplicates here. You tin likewise tell this is an instance of EQUI JOIN because inwards bring together predicate nosotros convey used = or equal condition. In fact, this ane is an instance of INNER Join, SELF Join, together with EQUI Join at the same time.

If yous are confused near syntax of all possible bring together inwards SQL, hither is a handy diagram:

Self Join vs Equi Join

In the curt major deviation betwixt Self Join together with Equi Join inwards SQL is that Self Join requires solely ane tabular array piece most of Equi bring together is status used inwards bring together predicate. Since Equi Join is based on the status for comparison, it tin hap inwards whatever INNER, OUTER or SELF bring together inwards SQL.

That’s all on the difference betwixt Self Join together with Equi Join inwards SQL. Self Join is ane of the of import technique to solve many SQL query related employment where 2 columns of the tabular array comprise the same type of information e.g. hither emp_id together with dept_id are essentially same data.

Further Learning
Difference betwixt primary key together with candidate key inwards tabular array or SQL

Difference betwixt View together with Materialized persuasion inwards Database or SQL

What is deviation betwixt clustered together with nonclustered index inwards SQL

10 ALTER ascendence instance inwards SQL

10 Oracle Interview Questions for SQL developer

10 SQL Query Interview Questions from Java Interviews

P.S. - If yous are looking for online training/course to larn SQL from scratch, I propose yous joining Introduction to SQL yesteryear Jon Flanders. It's ane of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering together with SQL query optimization.

Friday, March 27, 2020

Purging Tables Inward Sql Using Truncate As Well As Delete Command

While working alongside the database nosotros often demand to delete data, sometimes to practise to a greater extent than space, sometimes only take all information to brand tabular array laid upwards for adjacent day's transaction or sometimes only selectively take stale data. SQL provides a duo of handy commands to take information e.g. truncate, delete together with drop. The terminal 1 is flake unlike because instead of removing information it only deletes table. What is the divergence betwixt truncate together with delete ascendency inward SQL or when to utilization truncate vs delete is 1 of the almost frequently asked SQL interview question? Knowledge of this critical ascendency is non alone useful for interviews, but also land working alongside a large database alongside a large set out of records, peculiarly land writing SQL scripts for purging transaction databases. An Incorrect selection of SQL ascendency tin give notice effect is either real wearisome processor tin give notice fifty-fifty blow upwards the log segment; if likewise much information needs to endure removed together with log segment is non enough. That's why it's critical to know when to utilization truncate together with delete ascendency inward SQL.

In this article, nosotros volition kickoff meet few differences betwixt them, together with based upon them, y'all should endure able to honor out when DELETE is a meliorate choice for removing information or TRUNCATE should endure used to purge tables.

TRUNCATE vs DELETE inward SQL

As I said both commands are used to take information inward SQL, DELETE is to a greater extent than mutual than TRUNCATE equally it's component subdivision of essential CRUD (Create, Read, Update, together with Delete) operation, but at that spot are subtle differences betwixt them. Let's meet them kickoff :

1) The kickoff together with almost of import divergence betwixt TRUNCATE together with DELETE ascendency inward SQL is that truncate doesn't log row marker details land delete is logged. Since TRUNCATE is non logged, it's non possible to rollback it e.g. inward Oracle. But around database may furnish rollback functionality for truncate, on the other hand, DELETE is ever logged together with tin give notice endure rolled back.

2) Due to higher upwards reason, TRUNCATE ascendency is much faster than DELETE, together with due to same reason, y'all should non utilization DELETE to take large information set, since every delete functioning is logged, it may endure possible that your log segment gets filled together with blew up, if y'all endeavour to empty a large tabular array using DELETE command.

3) One syntactical divergence betwixt DELETE together with TRUNCATE is that erstwhile is a DML ascendency land subsequently is a DDL command.

4) Another critical divergence betwixt TRUNCATE together with DELETE ascendency inward SQL is that erstwhile reset whatever Identity column land DELETE retains the value of identity column.

5) DELETE ascendency piece of job at row marker together with acquire the row marker lock land TRUNCATE locks the whole table, instead of private rows.

6) One to a greater extent than divergence betwixt TRUNCATE vs DELETE comes from the fact nosotros discussed inward the kickoff option, it doesn't activate the trigger, because it doesn't log private row deletion, land DELETE activates the trigger, because of row marker logging.

7) Truncate is ordinarily used to take all information from tables e.g. inward social club to empty a particular tabular array together with y'all tin give notice define whatever filter based upon WHERE clause, but with DELETE, y'all tin give notice define the status inward WHERE clause for removing information from tables.

8) Since TRUNCATE is a DDL operation, it's automatically acquire committed, on the other hand, DELETE is non automobile commit.

While working alongside the database nosotros often demand to delete information Purging Tables inward SQL Using TRUNCATE together with DELETE command

That's all on the difference betwixt TRUNCATE together with DELETE ascendency inward SQL. As nosotros learned, y'all should truncate if y'all desire to completely empty your tables, if truncate is non that y'all desire then, y'all should hold off at batch delete option. Batch delete is truly piffling faster than deleting private rows together with also doesn't blew log segment.

Related SQL Interview Questions alongside Answers
Difference betwixt Primary telephone substitution together with Candidate telephone substitution inward SQL
Difference betwixt Clustered Index together with NonClustered Index inward database
Difference betwixt View together with Materialized View inward database
Difference betwixt Correlated together with Noncorrelated subquery inward SQL
How to bring together multiple tables inward unmarried SQL Query

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Wednesday, December 11, 2019

What Is Divergence Betwixt Spousal Human Relationship Vs Spousal Human Relationship All Inward Sql?

Though both UNION together with UNION ALL is used to combine results of 2 SELECT queries, the principal departure betwixt them is that UNION doesn't include duplicate tape but UNION ALL does. Another departure betwixt them is that UNION ALL is faster than UNION but may hold off tedious because it returns to a greater extent than information which takes to a greater extent than fourth dimension to go via the network. The departure betwixt UNION together with UNION ALL is ane of the tricky SQL questions, peculiarly for developers, who has non used this useful keyword ever. Since UNION clause is non every bit mutual every bit a SELECT clause inwards SQL, it's unremarkably asked inwards a telephonic circular of programming interviews to banking venture check whether the candidate is comfortable alongside SQL or not. It's inwards the same league of questions similar clustered vs non-clustered index or primary vs unique key. UNION is really unlike than other SQL commands because it operates on information rather than columns.

Anyway, respond of this query is simple, though both UNION together with UNION ALL are used to combine number of 2 split SQL queries on same or unlike table, UNION does non perish along duplicate tape (a row is considered duplicate if value of all columns are same), spell UNION ALL does.

Since you lot generally don't desire duplicate rows, UNION is preferred over UNION ALL inwards reporting together with application development. By the way, you lot should perish along inwards take heed that UNION ALL functioning amend than UNION because it doesn't own got to take away duplicate, thence no extra work.

This keyword is really good supported past times all major database e.g. Oracle, Microsoft SQL Server, MySQL together with PostgreSQL. Another affair to perish along inwards take heed is amount information returned past times UNION ALL; if your database server is quite far away together with you lot own got limited bandwidth, UNION ALL may look slower than UNION because of number of duplicates it returned.

Cost of transferring duplicate row tin forcefulness out come about the query execution benefits inwards many cases. We volition encounter pair of examples UNION together with UNION ALL inwards SQL, but earlier that few things to perish along inwards mind. In venture to combine results of 2 queries, they must comprise same number of columns.

For representative if ane query contains 3 columns together with other contains iv columns together with thence you lot tin forcefulness out non role UNION or UNION ALL. This is because a row volition solely live on considered duplicated when all column volition own got same value, irrespective of cite of columns itself.

UNION together with UNION ALL Example inwards SQL Server

Let's encounter ane elementary representative of UNION together with UNION ALL, this volition non solely demo you lot how they go but besides where you lot tin forcefulness out role it. This representative is from my sample database together with next screenshot is from SQL Server Management Studio 2014. We own got 2 tables, Employee together with Customer. In venture to role UNION together with UNION ALL, I own got kept same persons every bit employee together with customer, thence you lot volition encounter same id on emp_id together with customer_id, together with same cite every bit well. If you lot hold off at number of offset 2 select queries, you lot volition encounter that offset query returns 2 rows together with bit query returns 3 rows, where 2 rows own got just same information every bit offset query. Key things to banknote is that column names are unlike inwards both number set, offset ane has emp_id together with emp_name, spell bit information ready has customer_id together with customer_name, but most of import both dataset has solely 2 columns. This is must inwards venture to combine them using UNION together with UNION ALL keywords. Third query is an representative of how to role UNION clause inwards SQL, you lot tin forcefulness out encounter that combined number has only 3 columns, all are unique. Duplicate columns from bit number ready was non included. This is to a greater extent than similar how you lot do UNION inwards Set theory, where terminal number contains information from both set. Fourth query is how you lot should role UNION ALL, it contains v rows, 2 from offset query together with 3 from bit query. It has non removed duplicate rows from bit query, that's why you lot encounter Ken together with Bob repeating twice. This representative teaches us nub concept that UNION doesn't depend upon column cite but the data. You tin forcefulness out combine number of every bit many queries every bit possible until number of columns inwards all of them is same together with information is from same set.

Though both UNION together with UNION ALL is used to combine results of 2 SELECT queries What is departure betwixt UNION vs UNION ALL inwards SQL?

Regarding performance, you lot demand to run UNION together with UNION ALL alongside large database, containing millions of rows. There you lot tin forcefulness out monitor how much fourth dimension both takes together with compare them. Theoretically UNION ALL should own got less fourth dimension to execute but to a greater extent than fourth dimension to transfer information to client.

Difference betwixt UNION together with UNION ALL dominance inwards SQL

Now nosotros know how wedlock together with wedlock all plant together with has roughly background past times next higher upwards examples, let's summarise the similarities together with departure betwixt them for quick revision :

1) Both UNION together with UNION ALL are used to combine number of 2 split SQL query, it could live on on same tabular array or unlike tabular array but information should live on same. E.g. if product_id is used inwards 2 tabular array e.g. Product together with Order, together with thence 2 sql queries which pulls product_id from these 2 tabular array tin forcefulness out live on combined using UNION or UNION ALL.

2) Key departure betwixt UNION together with UNION ALL is that one-time volition take away duplicates but afterward volition perish along them. In roughly other words, UNION is equal to running distinct on output of UNION ALL. For example, if product_id 10 is returned past times both of SQL query together with thence it volition solely look in ane trial if you lot role UNION together with look twice if you lot role UNION ALL.

3) Due to higher upwards departure query execution fourth dimension of UNION ALL is smaller than UNION, which agency one-time runs faster than later. So if you lot desire faster output together with don't tending of duplicates role UNION ALL.

4) Keep inwards take heed that benefits gained past times non removing duplicates tin forcefulness out live on easily wiped out past times transferring to a greater extent than information over a piteous bandwidth network connection. That's why inwards practise roughly fourth dimension UNION ALL look slower than UNION because it provide lot of information alongside duplicates which require to a greater extent than fourth dimension to go from database server to client machine. So evaluate functioning of UNION together with UNION ALL representative past times case.

5) Another worth noting affair spell using UNION together with UNION ALL is that all queries combined using a UNION, INTERSECT or EXCEPT operator must own got an equal number of expressions inwards their target lists. For representative if number of query 1 has 3 column together with number of query 2 has 2 column together with thence you lot cannot combine them using UNION command.

That's all on difference betwixt UNION together with UNION ALL dominance inwards SQL. It's ane of the useful dominance to combine number of 2 SELECT queries when they comprise same data. There are many practical scenarios where UNION is really useful, for representative when you lot demand to do listing out of unlike tables containing information from same set. Main difference betwixt UNION together with UNION ALL is almost duplicates, one-time removes it spell afterward keeps it, other departure betwixt them on functioning together with networking bandwidth usage tin forcefulness out live on easily derived past times knowing this difference. Also perish along inwards take heed that it is good supported large 3 database e.g. MySQL, Oracle together with SQL Server. Let us know if you lot own got been asked this query inwards your SQL interview.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Second Highest Salary Inward Mysql Too Sql Server - Leetcode Solution

Write a SQL inquiry to acquire the 2d highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| i | 100 |
| 2 | 200 |
| iii | 300 |
+----+--------+

For example, given the higher upwardly Employee table, the 2d highest salary is 200. If in that place is no 2d highest salary, together with thus the inquiry should render NULL. You tin write SQL inquiry inward whatever of your favorite database e.g. MySQL, Microsoft SQL Server or Oracle. You tin also role database specific characteristic e.g. TOP, LIMIT or ROW_NUMBER to write SQL query, but you lot must also render a generic solution which should operate on all database. In fact, in that place are several ways to uncovering 2d highest salary together with you lot must know twain of them e.g. inward MySQL without using LIMIT keyword, inward SQL Server without using TOP together with inward Oracle without using RANK together with ROWNUM. Once you lot solve the problem, Interviewer volition most probable increase the difficulty degree past times either moving to Nth salary administration or taking away this buit-in utilities.

Second Highest Salary inward MySQL without LIMIT

Here is a generic SQL inquiry to uncovering 2d highest salary, which volition also operate fine inward MySQL. This solution uses subquery to start exclude the maximum salary from the information fix together with and thus over again finds maximum salary, which is effectively the 2d maximum salary from the Employee table.

SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

This volition render 200 inward our case.

Here is to a greater extent than or less other solution which uses sub inquiry but instead of IN clause it uses < operator

SELECT MAX(Salary) From Employee WHERE Salary < ( SELECT Max(Salary) FROM Employee);

You tin role this SQL inquiry if Interviewer inquire you lot to acquire 2d highest salary inward MySQL without using LIMIT. You tin also role distinct keyword if your Employee tabular array may incorporate duplicate salary, In this instance in that place is no such record, thus I accept non used distinct.

Second Highest Salary using Correlated SubQuery

Previous SQL inquiry was also using subquery but it was non-correlated, this solution volition role correlated subquery. This is also generic solution to find Nth highest salary inward Employee table. For each tape processed past times outer query, inner inquiry volition last executed together with volition render how many records has records has salary less than the electrical current salary. If you lot are looking for 2d highest salary together with thus your inquiry volition halt equally before long equally inner inquiry volition render 2.

SELECT Id, Salary FROM Employee e WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee p WHERE e.Salary<=p.Salary)

By the way, If you lot don't know divergence betwixt correlated together with non-correlated sub-query, run across here.

Second Maximum Salary inward MySQL using LIMIT

MySQL has a particular keyword called LIMIT which tin last used to limit the outcome fix e.g. it volition allow you lot to run across start few rows, terminal few rows or hit of rows. You tin role this keyword to uncovering the second, 3rd or Nth highest salary. Just role social club past times clause to kind the outcome fix together with thus impress the 2d salary equally shown below :

SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;

In this solution, nosotros accept start sorted all salaries cast Employee tabular array inward decreasing order, thus that 2 highest salaries come upwardly at tumble out of the outcome set. After that nosotros took merely 2 records past times using LIMIT 2. Again nosotros did the same affair but this fourth dimension nosotros kind the outcome assail ascending order, thus that 2d highest salary comes at top. Now nosotros impress that salary past times using LIMIT 1. Simple together with easy, right?

Second Highest Salary using SQL Server Top Keyword

Just similar MySQL has LIMIT keyword, which is immensely helpful inward sorting together with paging, Microsoft SQL Server also has a particular keyword called TOP, which equally refer propose prints tumble out records from outcome set. You tin impress tumble out 10 records past times maxim TOP 10. I oftentimes role this keyword to run across the information from a large table, merely to sympathise columns together with information within it. Here is the SQL inquiry to uncovering 2d maximum salary inward SQL Server :

SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable ORDER BY Salary ASC;

Here is the output of higher upwardly inquiry running on Microsoft SQL Server 2014 :

Write a SQL inquiry to acquire the 2d highest salary from the Employee tabular array Second Highest Salary inward MySQL together with SQL Server - LeetCode Solution

Now It's fourth dimension to apply the cognition you lot accept learned thus far. Solve next SQL queries at your convenience :

Write SQL inquiry to acquire 3rd highest salary from Employee table?
How create you lot uncovering fourth highest salary inward MySQL without using LIMIT keyword?
Write SQL inquiry to uncovering 2d highest salary inward Oracle database using ROWNUM?
How to uncovering Nth highest salary inward SQL Server without using TOP keyword?
Find 2d highest salary inward Oracle using rank?
How to uncovering tumble out iii salary inward Oracle without using ROW_NUMBER or RANK()?

That's all well-nigh different ways to uncovering Second highest Salary inward MySQL together with SQL Server. We accept seen examples to acquire 2d highest salary inward MySQL past times using LIMIT together with without using LIMIT. Similarly inward MSSQL nosotros know how to acquire 2d highest salary past times using TOP together with without using TOP keyword. I accept left the Oracle database for you lot equally an exercise. If you lot able to uncovering solution of all higher upwardly SQL queries inward quick fourth dimension together with feeling bore again, checkout my post well-nigh Top xx SQL queries from Interviews for to a greater extent than or less to a greater extent than fun.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Sunday, November 24, 2019

10 Oftentimes Asked Sql Query Interview Questions

In this article, I am giving around examples of SQL queries which is oftentimes asked when y'all give-up the ghost for a programming interview, having i or 2 twelvemonth sense on this field. Whether y'all give-up the ghost for Java developer position, QA, BA, supports professional, projection director or whatsoever other technical position, may interviewer await y'all to answer basic questions from Database together with SQL. It's too obvious that if y'all are working from i or 2 years on whatsoever projection in that place is expert gamble that y'all come upwardly across to handgrip database, writing SQL queries to insert, update, delete together with pick out records. One unproblematic but effective agency to banking concern check candidate's SQL science is yesteryear bespeak these types of unproblematic query. They are are neither really complex nor really big, but even so they encompass all cardinal concept a programmer should know virtually SQL.

These queries examine your SQL science on Joins, both INNER together with OUTER join, filtering records yesteryear using WHERE together with HAVING clause, grouping records using GROUP BY clause, calculating sum, average together with counting records using aggregate function similar AVG(), SUM() together with COUNT(), searching records using wildcards inwards LIKE operator, searching records inwards a outpouring using BETWEEN together with IN clause, DATE together with TIME queries etc. If y'all direct maintain faced whatsoever interesting SQL query or y'all direct maintain whatsoever occupation together with searching for the solution, y'all tin post service it hither for everyone's benefit. If y'all are looking for to a greater extent than challenging SQL query exercises together with puzzles together with then y'all tin too check Joe Cleko's SQL Puzzles And Answers, i of the best books to actually banking concern check together with ameliorate your SQL skills.

SQL Query Interview Questions together with Answers

Question 1: SQL Query to detect minute highest salary of Employee

Answer: There are many ways to detect minute highest salary of Employee inwards SQL, y'all tin either exercise SQL Join or Subquery to solve this problem. Here is SQL query using Subquery:

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

See How to detect minute highest salary inwards SQL for to a greater extent than ways to solve this problem.

Question 2: SQL Query to detect Max Salary from each department.

Answer: You tin detect the maximum salary for each subdivision yesteryear grouping all records yesteryear DeptId together with and then using MAX() component to calculate maximum salary inwards each grouping or each department.

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

These questions give-up the ghost to a greater extent than interesting if Interviewer volition inquire y'all to impress subdivision get upwardly instead of subdivision id, inwards that case, y'all demand to bring together Employee tabular array alongside Department using unusual cardinal DeptID, brand certain y'all create LEFT or RIGHT OUTER JOIN to include departments without whatsoever employee equally well. Here is the query

SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

In this query, nosotros direct maintain used RIGHT OUTER JOIN because nosotros demand the get upwardly of the subdivision from Department tabular array which is on the right side of JOIN clause, fifty-fifty if in that place is no reference of dept_id on Employee table.

Question 3: Write SQL Query to display the electrical current date.

Answer: SQL has built-in component called GetDate() which returns the electrical current timestamp. This volition piece of work inwards Microsoft SQL Server, other vendors similar Oracle together with MySQL too has equivalent functions.

SELECT GetDate();

Question 4: Write an SQL Query to banking concern check whether appointment passed to Query is the appointment of given format or not.

Answer: SQL has IsDate() component which is used to banking concern check passed value is a appointment or non of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL component together with it may non piece of work on Oracle, MySQL or whatsoever other database but in that place would last something similar.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY";

It volition render 0 because passed appointment is non inwards right format.

Question 5: Write an SQL Query to impress the get upwardly of the distinct employee whose DOB is betwixt 01/01/1960 to 31/12/1975.

Answer: This SQL query is tricky, but y'all tin exercise BETWEEN clause to larn all records whose appointment autumn betwixt 2 dates.

SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Question 6: Write an SQL Query detect seat out of employees according to gender whose DOB is betwixt 01/01/1960 to 31/12/1975.

Answer :

SELECT COUNT(*), sexual activity from Employees  WHERE  DOB BETWEEN '01/01/1960' AND '31/12/1975'  GROUP BY sex;

Question 7: Write an SQL Query to detect an employee whose Salary is equal or greater than 10000.

Answer :

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

Question 8: Write an SQL Query to detect get upwardly of employee whose get upwardly Start alongside ‘M’

Answer :

SELECT * FROM Employees WHERE EmpName like 'M%';

Question 9: detect all Employee records containing the give-and-take "Joe", regardless of whether it was stored equally JOE, Joe, or joe.

Answer :

SELECT * from Employees  WHERE  UPPER(EmpName) like '%JOE%';

Question 10: Write an SQL Query to find the year from date.

Answer: Here is how y'all tin detect Year from a Date inwards SQL Server 2008

SELECT YEAR(GETDATE()) as "Year";

Question 11: Write SQL Query to detect duplicate rows inwards a database? together with and then write SQL query to delete them?
Answer: You tin exercise the next query to pick out distinct records:

SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)

to Delete:

DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

Question 12: There is a tabular array which contains 2 column Student together with Marks, y'all demand to detect all the students, whose marks are greater than average marks i.e. listing of higher upwardly average students.
Answer: This query tin last written using subquery equally shown below:

SELECT student, marks from tabular array where marks > SELECT AVG(marks) from table)

I am giving around examples of SQL queries which is oftentimes asked when y'all give-up the ghost for a prog 10 Frequently asked SQL Query Interview Questions

Question 13: How create y'all detect all employees which are too manager? .
You direct maintain given a measure employee tabular array alongside an additional column mgr_id, which contains employee id of the manager.

Answer: You demand to know virtually self-join to solve this problem. In Self Join, y'all tin bring together 2 instances of the same tabular array to detect out additional details equally shown below

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

this volition demo employee get upwardly together with director get upwardly inwards 2 column e.g.

get upwardly manager_name
John David

One follow-up is to alter this query to include employees which don't direct maintain a manager. To solve that, instead of using the inner join, only exercise left outer join, this volition too include employees without managers.

Question 14: You direct maintain a composite index of 3 columns, together with y'all entirely render the value of 2 columns inwards WHERE clause of a pick out query? Will Index last used for this operation? For illustration if Index is on EmpId, EmpFirstName, together with EmpSecondName and y'all write query like

SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'

If the given 2 columns are secondary index column together with then the index volition non invoke, but if the given 2 columns comprise the principal index(first column spell creating index) together with then the index volition invoke. In this case, Index volition last used because EmpId and EmpFirstName are principal columns.

Hope this article volition assistance y'all to accept a quick do whenever y'all are going to attend whatsoever interview together with non direct maintain much fourth dimension to give-up the ghost into the deep of each query, but if y'all direct maintain expert fourth dimension to create together with then I advise y'all to read together with solve SQL queries from Joe Celko's SQL Puzzles together with Answers, Second edition, i of the best mass for SQL query lovers together with enthusiastic.

Other Interview Questions posts from Blog

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners