SQL

Question 1

Given a table Employee having columns empName and empId, what will be the result of the SQL query below?

select empName from Employee order by 2 desc;

Answer 1

“Order by 2” is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so “Order by 2” will cause the statement to throw an error while executing the above sql query.

Question 2

Write a SQL query to find the 10th tallest peak (“Elevation”) from a “Mountain” table. Assume that there are at least 10 records in the Mountain table. Explain your answer.

Answer 2

SELECT DISTINCT TOP (10) Elevation FROM Mountain ORDER BY Elevation DESC

Question 3

What is a NULL value? what are the differences with zeros or a blank space

Answer 3

A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.

As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.
Null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).

Question 4

How do you return just a limit amount of lines from a sql table?

Answer 4

You can use sql select top (or LIMIT or ROWNUM Clause)

Question 5

Is the command JOIN an inner JOIN?

Answer 5

yes

Question 6

What can you use in a WHERE clause to search for a specified pattern in a column??

Answer 6

LIKE

SELECT * FROM Customers
WHERE CustomerName LIKE ‘_r%’;

There are two wildcards often used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters

_ - The underscore represents a single character

Question 7

How do you add a column to a table?

Answer 7

The ALTER TABLE statement is used to modify the columns of an existing table. When combined with the ADD COLUMN clause, it is used to add a new column.

ALTER TABLE table_name

ADD column_name datatype;

Question 8

How to order elemets in a quey

Answer 8

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

Question 9

Can you use = or != operator to look for NULL values?

Answer 9

Not you have to use the

IS NULL or

IS NOT NULL

operator

Question 10

Table is as follows:

ID C1 C2 C3

1 Red Yellow Blue

2 NULL Red Green

3 Yellow NULL Violet

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using OR.

Answer 10

SELECT * FROM table WHERE ‘Yellow’ IN (C1, C2, C3)

Question 11

What is an index in an SQL database?

Answer 11

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

Question 12

Get all the attributes from the table

Answer 12

SELECT * FROM STUDENT WHERE ROLL_NO>2;

Question 13

How would you get two attributes from a table in SQL?

Answer 13

If we want to retrieve attributes ROLL_NO and NAME of all students, the query will be:

SELECT ROLL_NO, NAME FROM STUDENT;

Question 14

Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?

Answer 14

You can avoid duplicates using UNION ALL and still run much faster than UNION DISTINCT (which is actually same as UNION) by running a query like this:

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command, while avoiding much of its performance hit.

Question 15

How can you remove rows from. a table?

Answer 15

The DELETE statement is used to delete records (rows) in a table. The WHERE clause specifies which record or records that should be deleted. If the WHERE clause is omitted, all records will be deleted.

DELETE FROM table_name

WHERE some_column = some_value;

Question 16

How would you query attributes in order?

Answer 16

SELECT * FROM STUDENT ORDER BY AGE ASC;

SELECT * FROM STUDENT ORDER BY AGE DESC;

Question 17

Tell us more about the GROUPBY clause?

What is it used for?

Answer 17

GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

• The GROUP BY clause groups records into summary rows.
• GROUP BY returns one records for each group.
• GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc.
• GROUP BY can group by one or more columns.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

if you want the custumer id and the country you need a groupby otherwise you can call this without groupby and get total count.

example with join

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

Question 18

BETWEEN Operator

Answer 18

The BETWEEN operator can be used to filter by a range of values. The range of values can be text, numbers or date data. The given query will match any movie made between the years 1980 and 1990, inclusive.

SELECT * FROM movies WHERE year BETWEEN 1980 AND 1990;

Question 19

In a database what are Table, Field, Record and column, what do they refer to?

Answer 19

A table is a database object used to store records in a field in the form of columns and rows that holds data.

A field in a Database table is a space allocated to store a particular record within a table.

A record (also called a row of data) is an ordered set of related data in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Question 20

How do you remove an element from a table?

Answer 20

You can use the DELETE command.

DELETE FROM table_name WHERE some_condition;

table_name: name of the table some_condition: condition to choose particular record.

Where of course is used to selectively delete stuff

Question 21

What is wrong with this SQL query? Correct it so it executes properly.

SELECT Id, YEAR(BillingDate) AS BillingYear FROM Invoices WHERE BillingYear >= 2010;

Answer 21

The expression BillingYear in the WHERE clause is invalid. Even though it is defined as an alias in the SELECT phrase, which appears before the WHERE phrase, the logical processing order of the phrases of the statement is different from the written order. Most programmers are accustomed to code statements being processed generally top-to-bottom or left-to-right, but T-SQL processes phrases in a different order.

The correct query should be:

SELECT Id, YEAR(BillingDate) AS BillingYear FROM Invoices WHERE YEAR(BillingDate) >= 2010;

Question 22

AS Clause:

Answer 22

Columns or tables in SQL can be aliased using the AS clause. This allows columns or tables to be specifically renamed in the returned result set. The given query will return a result set with the column for name renamed to movie_title.

SELECT name AS ‘movie_title’ FROM movies;

Question 23

What do you mean by foreign key?

Answer 23

A Foreign key is a field which can uniquely identify each row in another table. And this constraint is used to specify a field as Foreign key. That is, this field points to primary key of another table. This usually creates a kind of link between the two tables.

CREATE TABLE Orders ( O_ID int NOT NULL, ORDER_NO int NOT NULL, C_ID int, PRIMARY KEY (O_ID), FOREIGN KEY (C_ID) REFERENCES Customers(C_ID) )

Question 24

How does aliases work in SQL?

Answer 24

Aliases are the temporary names given to table or column for the purpose of a particular SQL query

Aliases are created to make table or column names more readable. The renaming is just a temporary change and table name does not change in the original database. Aliases are useful when table or column names are big or not very readable. These are preferred when there are more than one table involved in a query

SELECT column FROM table_name as alias_name;

SELECT column as alias_name FROM table_name;

Question 25

What is a primary key?

Answer 25

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

Question 26

What is the difference between BETWEEN and IN operators in SQL?

Answer 26

The BETWEEN operator is used to fetch rows based on a range of values.
For example,

SELECT * FROM Students WHERE ROLL_NO BETWEEN 20 AND 30;​

IN
The IN operator is used to check for values contained in specific sets.
For example,

SELECT * FROM Students WHERE ROLL_NO IN (20,21,23);

Question 27

Given a table name A with a column x —— 2 -2 4 -4 -3 0 2

Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x.

Answer 27

select sum(case when x>0 then x else 0 end)sum_pos,sum(case when x<0 then x else 0 end)sum_neg from a;

Question 28

Is SQL executed in the logical order?

Answer 28

No as long as the result is the same as the logical order the optimizer according to the schema and how data are saved on disk is free to use the order it wants

Question 29

Write a query to insert/update Col2’s values to look exactly opposite to Col1’s values.

Col1Col2

10

01

01

01

10

01

10

10

Answer 29

update table set col2 = case when col1 = 1 then 0 else 1 end

Or if the type is numeric:

update table set col2 = 1 - col1

Question 30

JOIN clause and format

Answer 30

The JOIN clause allows for the return of results from more than one table by joining them together with other results based on common column values specified using an ON clause. INNER JOIN is the default JOIN and it will only return results matching the condition specified by ON.

SELECT * FROM books JOIN authors ON books.author_id = authors.id;

Question 31

How do you add a row to a table? command and examples

Answer 31

The INSERT INTO statement is used to add a new record (row) to a table.

It has two forms as shown:

Insert into columns in order.

Insert into columns by nam

– Insert into columns in order: INSERT INTO table_name VALUES (value1, value2); – Insert into columns by name: INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Question 32

WITH Clause

Answer 32

The WITH clause stores the result of a query in a temporary table (temporary_movies) using an alias.

Multiple temporary tables can be defined with one instance of the WITH keyword.

Question 33

How would you create a table with columns constraints?

What constrains are available?

Answer 33

Column constraints are the rules applied to the values of individual columns:

PRIMARY KEY constraint can be used to uniquely identify the row.

UNIQUE columns have a different value for every row.

NOT NULL columns must have a value.

DEFAULT assigns a default value for the column when no value is specified.

There can be only one PRIMARY KEY column per table and multiple UNIQUE columns.

CREATE TABLE student

( id INTEGER PRIMARY KEY,

name TEXT UNIQUE,

grade INTEGER NOT NULL,

age INTEGER DEFAULT 10

);

Question 34

Using the BETWEEN operator, write a query that selects all information about movies whose name begins with the letters ‘D’, ‘E’, and ‘F’.

Answer 34

SELECT *

FROM movies

WHERE name BETWEEN “D” and “G”;

Question 35

How do you get only a unique list of attributes wih no repetitions?

Answer 35

SELECT DISTINCT ADDRESS FROM STUDENT;

Question 36

How do you get the Nth-highest salary from the Employee table in SQL

Answer 36

SELECT salary from Employee order by salary DESC LIMIT 2,1

Question 37

Given a table with three column, (id, category, value) and each id has 3 or less category (price, size, color). Now, how can I find those id’s for which the value of two or more category matches to one another? For eg: ID1 (price 10, size M, color Red), ID2 (price 10, Size L, Color Red) , ID3 (price 15, size L, color Red) Then the output should be two rows: ID1 ID2
and ID2 ID3

Question 38

What is the command “like” doing?

Answer 38

Is selecting for patterns like containing a letter in the names of the query

SELECT SupplierID, Name, Address FROM Suppliers WHERE Name LIKE ‘Ca%’;

The LIKE operator is extremely resourceful in situations such as address filtering wherein we know only a segment or a portion of the entire address (such as locality or city) and would like to retrieve results based on that. The wildcards can be resourcefully exploited to yield even better and more filtered tuples based on the requirement.

Question 39

What does UNION do in SQL?

Answer 39

The Union Clause is used to combine two separate select statements and produce the result set as a union of both the select statements.

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; Resultant set consists of distinct values.

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2; Resultant set consists of duplicate values too.

Question 40

query so that it selects all the information about the movie titles that begin with the word ‘The’.

Answer 40

SELECT *

FROM movies

where name LIKE “The %”;

Question 41

What if you want to get attributes from a table with a condition?

Answer 41

SELECT ROLL_NO, NAME FROM STUDENT WHERE ROLL_NO>2;

Question 42

What are tables and Fields?

Answer 42

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

Example:.

Table: Employee.

Field: Emp ID, Emp Name, Date of Birth.

Data: 201456, David, 11/15/1960.

Question 43

What is the difference between DELETE and TRUNCATE commands?

Answer 43

DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.

TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

Question 44

Can you use arithmetic operation in a SQL query?

Answer 44

Yes you can :

for example

SELECT employee_id, employee_name, salary, salary % employee_id AS “salary % employee_id” FROM addition;

SELECT employee_id, employee_name, salary, salary * 100 AS “salary * 100” FROM addition;

Question 45

Remind yourself the syntax to create a table in SQL

Answer 45

CREATE TABLE Orders

( O_ID int NOT NULL,

ORDER_NO int NOT NULL,

C_ID int,

PRIMARY KEY (O_ID),

FOREIGN KEY (C_ID) REFERENCES Customers(C_ID) )

Question 46

What is the use of select top? When is it useful?

Answer 46

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

Mysql use limit oracle ROWNUM

Question 47

What is the structure for queries in SQL?

Answer 47

So first we will consider the Data Query Language. A generic query to retrieve from a relational database is:

• SELECT [DISTINCT] Attribute_List FROM R1,R2….RM
• [WHERE condition]
• [GROUP BY (Attributes)[HAVING condition]]
• [ORDER BY(Attributes)[DESC]];

Question 48

CROSS JOIN Clause

Answer 48

The CROSS JOIN clause is used to combine each row from one table with each row from another in the result set. This JOIN is helpful for creating all possible combinations for the records (rows) in two tables.

The given query will select the shirt_color and pants_color columns from the result set, which will contain all combinations of combining the rows in the shirts and pants tables. If there are 3 different shirt colors in the shirts table and 5 different pants colors in the pants table then the result set will contain 3 x 5 = 15 rows.

SELECT shirts.shirt_color, pants.pants_color FROM shirts CROSS JOIN pants;

Question 49

Write an SQL query to find names of employee start with ‘A’

Answer 49

SELECT * FROM Employees WHERE EmpName like ‘A%’ ;

SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern; LIKE: operator name pattern: exact value extracted from the pattern to get related data in result set.

Question 50

What are the sql constrains for column when you create a table?

Answer 50

NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column anymore.

UNIQUE: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated.

PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table. And this constraint is used to specify a field in a table as primary key.

FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as the Foreign key.

CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition.

DEFAULT: This constraint specifies a default value for the column when no value is specified by the user.

Question 51

What is the difference between union and union_all

Answer 51

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

Question 52

What is SQL?

Answer 52

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Standard SQL Commands are Select.

Question 53

What is the HAVING clause used for?

Answer 53

HAVING clause: This clause is used in association with GROUP BY clause. It is applied to the each group of result or the entire result as single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause

Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name
HAVING condition;

Question 54

How do you modify elements in a table?

Answer 54

The UPDATE statement is used to edit records (rows) in a table. It includes a SET clause that indicates the column to edit and a WHERE clause for specifying the record(s).

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE some_column = some_value;

Question 55

Left join

Answer 55

An outer join will combine rows from different tables even if the join condition is not met. In a LEFT JOIN, every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table.

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Question 56

How to find a duplicate record?

duplicate records with one field

duplicate records with more than one field

Answer 56

duplicate records with one field

SELECT name, COUNT(email) FROM users GROUP BY email HAVING COUNT(email) > 1

duplicate records with more than one field

SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) >

Question 57

The SQL LIKE Operator

Answer 57

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters

_ - The underscore represents a single character

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%or%’;

Question 58

What is a join in SQL? What are the types of joins?

Answer 58

An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.

• INNER JOIN:
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Question 59

Select value for which one column is null

Answer 59

SELECT name FROM movies WHERE imdb_rating IS NOT NULL;

Question 60

How do you copy data from one table to another table ?

Answer 60

INSERT INTO table2 (column1, column2, column3, …)

SELECT column1, column2, column3, …

FROM table1

WHERE condition;

Question 61

How to fetch common records from two tables?

Answer 61

Common records result set can be achieved by -.

Select studentID from student. INTERSECT Select StudentID from Exam

Question 62

UNION Clause

Answer 62

The UNION clause is used to combine results that appear from multiple SELECT statements and filter duplicates.

For example, given a first_names table with a column name containing rows of data “James” and “Hermione”, and a last_names table with a column name containing rows of data “James”, “Hermione” and “Cassidy”, the result of this query would contain three names: “Cassidy”, “James”, and “Hermione”.

SELECT name FROM first_names UNION SELECT name FROM last_names

Question 63

Mention the important SQL clause and their schematic order?

Answer 63

Question 64

What is the logical order of SQL?

Answer 64

. FROM

2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. SELECT
7. DISTINCT
8. ORDER BY

Question 65

How can you get elements without a given condition like name of students not taking a clasee etc

Answer 65

You can use both the except or the not in clause

SELECT * FROM Customers WHERE NOT Country=’USA’ AND NOT Country=’UK’;

except work more in a set point of view (like intersect does for example)

SELECT column-1, column-2 …… FROM table 1 WHERE….. EXCEPT SELECT column-1, column-2 …… FROM table 2 WHERE…..

Question 66

What is the difference between the WHERE and HAVING clauses?

Answer 66

The difference between the having and where clause in SQL is that the where clause cannot be used with aggregates, but the having clause can.

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BY is used:

The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.

SELECT Student, Score FROM Marks WHERE Score >=40

This would select data row by row basis.

The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

Question 67

what does a query with not in (some list) return if some list contains any NULL element?

Answer 67

surprisingly it returns NULL so you have to remove null before doing that.

Question 68

How do you update F as M and M as F from the below table TestTable?

John | M |
| Emma | F |
| Mark | M |
| Anne | F |
————————

Answer 68

UPDATE TestTable SET Gender = CASE Gender WHEN ‘F’ THEN ‘M’ ELSE ‘F’ END

Question 69

4. What is a Database?

Answer 69

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Example: School Management Database, Bank Management Database.

Question 70

Can you write a conditional expression in sqSQLwhat is the syntax?

Answer 70

The CASE Expression: Let you use IF-THEN-ELSE statements without having to invoke procedures.
In a simple CASE expression, the SQL searches for the first WHEN……THEN pair for which expr is equal to comparison_expr and returns return_expr. If above condition is not satisfied, an ELSE clause exists, the SQL returns else_expr. Otherwise, returns NULL.
We cannot specify literal null for the return_expr and the else_expr. All of the expressions(expr, comparison_expr, return_expr) must be of the same data type.
Syntax:

CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 . . . WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

Example:

Input : SELECT first_name, dpartment_id, salary, CASE dpartment_id WHEN 50 THEN 1.5*salary WHEN 12 THEN 2.0*salary ELSE salary END “REVISED SALARY” FROM Employee;

Question 71

For a table orders having a column defined simply as customer_id VARCHAR(100), consider the following two query results:

SELECT count(*) AS total FROM orders;

+——-+

+——-+

| 100 |

+——-+

SELECT count(*) AS cust_123_total FROM orders WHERE customer_id = ‘123’;

+—————-+

| cust_123_total |

+—————-+

| 15 |

+—————-+

Given the above query results, what will be the result of the query below?

SELECT count(*) AS cust_not_123_total FROM orders WHERE customer_id <> ‘123’;

total |

Answer 71

The obvious answer is 85 (i.e, 100 - 15). However, that is not necessarily correct. Specifically, any records with a customer_id of NULL will not be included in either count (i.e., they won’t be included in cust_123_total, nor will they be included in cust_not_123_total). For example, if exactly one of the 100 customers has a NULL customer_id, the result of the last query will be:

Question 72

How to select all the even number records from a table?

Answer 72

Select * from table where id % 2 = 0

Question 73

How would you rename a column as you like in the output of a SELECT query?

Answer 73

AS clause

SELECT name AS ‘movie_title’ FROM movies;

Question 74

How can you select RANGES of values in a WHERE cluase?

Answer 74

BETWEEN Operator

Question 75

iven a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table?

(Assume the table contains well over 100 records with odd user_id values.)

Answer 75

SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id

Question 76

How do you get the last id without the max function?

Answer 76

select top 1 id from table order by id desc

or

select id from table order by id desc limit 1

Question 77

What is the command to select for a patter in the query like getting a name that contains a string?

Answer 77

LIKE:

SELECT SupplierID, Name, Address FROM Suppliers WHERE Name LIKE ‘Ca%’;

There are two kinds of wildcards used to filter out the results:

% : Used to match zero or more characters. (Variable Length)

_ : Used to match exactly one character. (Fixed Length)

Question 78

How can you test for null value in a sql table?

Answer 78

A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, It is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL and IS NOT NULL operators.

SELECT column_names FROM table_name WHERE column_name IS NULL;

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

Question 79

What is the use of the SQL except clause?

Answer 79

n SQL, EXCEPT returns those tuples that are returned by the first SELECT operation, and not returned by the second SELECT operation.

The not in also exist in sql (not mysql)

This is the same as using a subtract operator in relational algebra.

SELECT Name FROM Students EXCEPT SELECT TutorName FROM TA;

EXCEPT automatically removes all duplicates in the final result, whereas NOT IN retains duplicate tuples. It is also important to note that EXCEPT is not supported by MySQL.

Question 80

What is the difference between primary key and unique constraints?

Answer 80

Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. The primary key creates the cluster index automatically but the Unique key does not.

Question 81

What is the peculiar format of the union clause?

Answer 81

SELECT name FROM first_names UNION SELECT name FROM last_names

Question 82

How to add new Employee details in an Employee_Details table with the following details
Employee_Name: John, Salary: 5500, Age: 29?

Answer 82

INSERT into Employee_Details (Employee_Name, Salary, Age) VALUES (‘John’, 5500 , 29);

Question 83

Explain how to add rows to an existing SQL table?

Answer 83

There are two ways of using INSERT INTO statement for inserting rows:

Only values:

INSERT INTO table_name VALUES (value1, value2, value3,…);

Column names and values both

INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2, value3,..);

This will return null if you do not pass all the values.

Question 84

How would you use group by to get the sum of the ages of all the students that live in the same address?

Answer 84

SELECT ADDRESS, SUM(AGE) FROM STUDENT GROUP BY (ADDRESS);

Question 85

CASE clause.

Answer 85

A CASE statement allows us to create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.

SELECT name,

CASE

WHEN imdb_rating > 8 THEN ‘Fantastic’

WHEN imdb_rating > 6 THEN ‘Poorly Received’

ELSE ‘Avoid at All Costs’

END

FROM movies;

Question 86

For instance, imagine that our customers table was out of date, and was missing any information on customer 11. If that customer had an order in orders, what would happen when we joined the tables?

with a simple JOIN

Answer 86

When we perform a simple JOIN (often called an inner join) our result only includes rows that match our ON condition.

Question 87

Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z, A-Z). Write a SQL query to print ‘Fizz’ for a numeric value or ‘Buzz’ for alphabetical value for all values in that column.

Example:

[‘d’, ‘x’, ‘T’, 8, ‘a’, 9, 6, 2, ‘V’]

…should output:

[‘Buzz’, ‘Buzz’, ‘Buzz’, ‘Fizz’, ‘Buzz’,’Fizz’, ‘Fizz’, ‘Fizz’, ‘Buzz’]

Answer 87

Remember just that you can write simple cases when then else end in SQL

SELECT col, case when upper(col) = lower(col) then ‘Fizz’ else ‘Buzz’ end as FizzBuzz from table;

Question 88

How do you change a row of a SQL table?

Answer 88

UPDATE statement in SQL is used to update the data of an existing table in database:

UPDATE table_name SET column1 = value1, column2 = value2,… WHERE condition;

UPDATE Student SET NAME = ‘PRATIK’ WHERE Age = 20;

If you omit the WHERE clause you will change all of them.

Question 89

What is the clause for an outer join?

Answer 89

LEFT JOIN

Question 90

What are the aggregation functions you can use on an attribute and how would you use those?

Answer 90

SELECT COUNT (PHONE) FROM STUDENT;

Count returns the number of rows in a relation

you also have max min and avg

Aggregation function only return 1 row!