12 SQL Queries Instance and Follow Questions for Tech Interviews

SQL Script to create a desk and Populate knowledge

On this part, we’ll see our SQL script for creating and populating the pattern desk required for working SQL queries. I’ve chosen Worker and Division desk to show you write SQL queries as a result of it is among the most standard SQL question examples and many of the builders, college students, and technical guys are aware of this scheme.

That is additionally the instance lots of you’ve utilized in your lecturers so it is fairly straightforward to grasp and correlate. Keep in mind, understanding schema and knowledge is essential not solely to jot down right SQL queries but in addition to confirm that your SQL question is right by wanting on the output.

The SQL queries are written for Microsoft SQL Server database and examined on the identical, however you may simply run on Oracle, MySQL, or another database of your alternative by eradicating T-SQL code e.g. the one which checks if a desk already exists, after which drop and re-create it.

Many of the code is normal ANSI SQL, therefore it can run as it’s on another database. If you happen to nonetheless face any issues then it’s also possible to verify this information emigrate SQL Server queries to Oracle.

SQL scripts to create tables 

USE Check
GO

-- drop Worker desk if already exists
IF OBJECT_ID('dbo.Worker', 'U') IS NOT NULL
BEGIN
  PRINT 'Worker Desk Exists, dropping it now'
 DROP TABLE Worker;
END

-- drop Division desk if already exists
IF OBJECT_ID('dbo.Division', 'U') IS NOT NULL
BEGIN
  PRINT 'Division Desk Exists, dropping it now'
  DROP TABLE Division;
END

-- create desk ddl statments
CREATE TABLE Worker(emp_id INTEGER PRIMARY KEY, dept_id INTEGER,
 mngr_id INTEGER, emp_name VARCHAR(20), wage INTEGER);
CREATE TABLE Division(dept_id INTEGER PRIMARY KEY, dept_name VARCHAR(20));

-- alter desk so as to add international keys
ALTER TABLE Worker ADD FOREIGN KEY (mngr_id)
 REFERENCES Worker(emp_id);
ALTER TABLE Worker ADD FOREIGN KEY (dept_id) 
REFERENCES Division(dept_id);

-- populating division desk with pattern knowledge
INSERT INTO Division (dept_id, dept_name) 
VALUES
(1, 'Finance'),
(2, 'Authorized'),
(3, 'IT'),
(4, 'Admin'),
(5, 'Empty Division');

-- populating worker desk with pattern knowledge
INSERT INTO Worker(emp_id, dept_id, mngr_id, emp_name, wage)
VALUES( 1, 1, 1, 'CEO', 100),
( 2, 3, 1, 'CTO', 95),
( 3, 2, 1, 'CFO', 100),
( 4, 3, 2, 'Java Developer', 90),
( 5, 3, 2, 'DBA', 90),
( 6, 4, 1, 'Adm 1', 20),
( 7, 4, 1, 'Adm 2', 110),
( 8, 3, 2, 'Internet Developer', 50),
( 9, 3, 1, 'Middleware', 60),
( 10, 2, 3, 'Authorized 1', 110),
( 11, 3, 3, 'Community', 80),
( 12, 3, 1, 'UNIX', 200);

This question runs on the Check database, if you do not have the Check database in your SQL Server occasion then both create it or take away the “USE Check” to run on any database of your alternative, it’s also possible to change the title of the database and maintain the “USE”.

Whenever you run this script, it can create and populate the information the primary time. Whenever you run it once more, it can drop and recreate the tables once more, as proven within the following output:

Worker Desk Exists, dropping it now
Division Desk Exists, dropping it now

(5 row(s) affected)

(12 row(s) affected)

And, right here is how our knowledge seems like after establishing: