Category: Database

SQL Brushup

http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries?showall=&limitstart=

Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in (‘John’,’Roy’)

Get employee details from employee table whose employee name are not “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME not in (‘John’,’Roy’)

Get employee details from employee table whose first name starts with ‘J’

Select * from EMPLOYEE where FIRST_NAME like ‘J%’

Get employee details from employee table whose first name contains ‘o’

Select * from EMPLOYEE where FIRST_NAME like ‘%o%’

Get employee details from employee table whose first name ends with ‘n’ and name contains 4 letters

Select * from EMPLOYEE where FIRST_NAME like ‘___n’ (Underscores)

Get employee details from employee table whose Salary between 500000 and 800000

Select * from EMPLOYEE where Salary between 500000 and 800000

Get employee details from employee table whose joining year is “2013”
SQL Queries in MySQL, Select * from EMPLOYEE where year(joining_date)=’2013′

Get database date
select now()

Get department wise average salary from employee table order by salary ascending
select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc

HAVING Clause
The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, we can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.

Notice that the HAVING clause applies the filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.

Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc

Select employee details from employee table if data exists in incentive table ?
select * from EMPLOYEE where exists (select * from INCENTIVES)

Since MySQL does not support MINUS operator here is one way to do it
Instead of

SELECT x, y FROM table_a
MINUS
SELECT x, y FROM table_b;

use
SELECT a.x, a.y
FROM table_a a LEFT JOIN table_b b
ON a.x = b.x AND a.y = b.y
WHERE b.x IS NULL;

Select 20 % of salary from John , 10% of Salary for Roy and for other 15 % of salary from employee table
SELECT FIRST_NAME, CASE FIRST_NAME WHEN ‘John’ THEN SALARY * .2 WHEN ‘Roy’ THEN SALARY * .10 ELSE SALARY * .15 END “Deduced_Amount” FROM EMPLOYEE

Select Last Name from employee table which contain only numbers
Select * from EMPLOYEE where lower(LAST_NAME)=upper(LAST_NAME)

Stored Procedure vs Function in MySQL

* Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes whenever it is called. But Function is compiled and executed every time when it is called.
Stored

* Procedures are used as scripts and functions are used as methods

* Functions have a scalar return value. Procedures do not have a return value.

* A stored procedure may have arguments that are IN, OUT, or INOUT. Functions may only have IN arguments.

* To invoke a stored procedure, use the CALL statement. To invoke a stored function, refer to it in an expression

MySQL Query Set

1)
http://www.crazyforcode.com/mysql-query-set-5/

We have 3 tables Movie, Reviewer, Rating as shown below:
Movie ( mID, title, year, director )
There is a movie with ID number mID, a title, a release year, and a director.

Reviewer ( rID, name )
The reviewer with ID number rID has a certain name.

Rating ( rID, mID, stars, ratingDate )
The reviewer rID gave the movie mIDa number of stars rating (1-5) on a certain ratingDate.

Q1. Find the titles of all movies that have no ratings.
Q2. For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer’s name and the title of the movie.

Ans 1) select title from movie where mid not in (select distinct mid from rating)

Ans 2) 
SELECT  NAME,TITLE FROM RATING AS R1,RATING AS R2,REVIEWER,MOVIE 
WHERE MOVIE.MID=R1.MID AND REVIEWER.RID=R1.RID 
AND R1.MID=R2.MID AND R1.RID = R2.RID 
AND R1.STARS < R2.STARS 
AND R1.RATINGDATE < R2.RATINGDATE 
ORDER BY R1.RATINGDATE ASC

How does MySQL uses indexes ?

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

Also usually MySQL just uses 1 index per query. In rare cases it may use multiple indexes for a query. This is called index-merge. Explanation of why MySQL uses just 1 index per query :
http://dba.stackexchange.com/questions/22014/does-mysql-have-a-limitation-of-one-index-per-query

MySQL 10 Interview Questions : Set-1

1) What is the difference between primary key and unique key ?

both primary and unique key uniquely identifies each row in table but there are some subtle difference between them. here are some of them :

1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc.

2) Primary key can be combination of more than one unique keys in same table.

3) There can be only one primary key per table in relation database e.g. MySQL, Oracle or Sybase but there can be more than one unique key per table.

4) Unique key is represented using unique constraint while primary key is created using primary key constraint in any table and it’s automatically gets unique constraint.

5) Many database engine automatically puts clustered index on primary key and since you can only have one clustered index per table, its not available to any other unique key at same time.

2) What is a clustered index?
With a clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index.

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

A clustered index means you are telling the database to store close values actually close to one another on the disk. This has the benefit of rapid scan / retrieval of records falling into some range of clustered index values.

Clustered Index
Only one per table.
Faster to read than non clustered as data is physically stored in index order.

Non­clustered Index
Can be used many times per table.
Quicker for insert and update operations than a clustered index.

3) How many triggers are possible in MySQL?
Answer : There are only six triggers are allowed to use in MySQL database and they are.
Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete

4) You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?

SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase “Found 13,450,600 results, displaying 1-10”. Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

5) How do you find out which auto increment was assigned on the last insert?

SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function.

Joins

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
(Tip: Read it as : All rows from Left + The Join)
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

CROSS JOIN: It gives us combinations of each row of first table with all records in second table
FULL JOIN:  a full outer join combines the effect of applying both left and right outer joins. Where rows in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those rows that do match, a single row will be produced in the result set (containing columns populated from both tables).

https://www.guru99.com/joins.html

Features of NoSql Databases

1) Elastic Scaling: Because of the distributed nature of non-relational databases, to scale NoSQL all you need to do is add machines to the cluster to meet demand. The new breed of NoSQL databases are designed to expand transparently to take advantage of new nodes, and they’re usually designed with low-cost commodity hardware in mind.

2) Big Data: They handle much bigger data volumes with relative ease.

3) Economics: NoSQL databases typically use clusters of cheap commodity servers to manage the exploding data and transaction volumes, while RDBMS tends to rely on expensive proprietary servers and storage systems.

4) Flexible Data Models: Even minor changes to the data model of an RDBMS have to be carefully managed and may necessitate downtime or reduced service levels. NoSQL databases have far more relaxed — or even nonexistent — data model restrictions. NoSQL Databases usually have “Eventual Consistency”.

5) Little Downtime: Because of their distributed nature, NoSQL databases can be pretty much always on. This is a huge advantage for web- and mobile-based businesses that can’t afford to be down for a single moment.

6) Auto-Sharding: NoSQL databases, on the other hand, usually support auto-sharding, meaning that they natively and automatically spread data across an arbitrary number of servers, without requiring the application to even be aware of the composition of the server pool. Data and query load are automatically balanced across servers, and when a server goes down, it can be quickly and transparently replaced with no application disruption.