Delete Duplicate Records

Write a SQL query to delete all duplicate email entries in a table, keeping only unique emails based on its smallest Id. +—-+———————-+ | Id | Email +—-+———————-+ | 1 | [email protected] | | 2 | [email protected] | | 3 | [email protected] | +—-+———————–+ Id is the primary key column for this table. For example, Read More →

Composite Index (Multiple-Column Indexes)

In the last post, We discussed about the basics of Indexing in database. In this post we will discuss about Composite Indexes and how ordering is important in composite indexes. We can have indexes on multiple columns which is also called Composite Index. In Mysql, An index may consist of up to 16 columns. MySQL Read More →

What is a Database Index?

What is a Database index? A database index is a data structure (most commonly B-tree) that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row Read More →

Clustered and Non Clustered Index

In SQL Server database there are mainly two types of indexes, Clustered index and Non Clustered index. For those, who are not aware of benefits of Index or why we use index in database, they help in making your SELECT query faster. you must index on columns which are frequently used in WHERE clause of Read More →

What is the difference between delete, truncate and drop?

TRUNCATE is DDL command. It is used to delete all the rows from table. You can not use where clause with truncate. It also free up the space containing the table. Truncate is executed by taking table lock. You can not retrieve data even after rollback is performed, in case of truncate as it does Read More →

Mysql Query – Set 6

We have 3 tables Highschooler, Friend, Likes as shown below: Highschooler ( ID, name, grade ) There is a high school student with unique ID and a given first name in a certain grade. Friend ( ID1, ID2 ) The student with ID1 is friends with the student with ID2. Friendship is mutual, so if Read More →

Keys in RDBMS

We know that database uses tables to organize information. To maintain data integrity (that is data should be correct and in well formed) we use concept of keys. There are five types of keys in database which is as follows – Candidate key Primary Key Foreign Key Alternate Key Composite Key Example: STUDENT {SID, FNAME, Read More →

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 ) Read More →

Explain ACID properties?

ACID stands for Atomicity Consistency Isolation Durablity Atomicity Modification on the data in the database either fail or succeed. The beginning of such a modification starts with a transaction and ends when a transaction finishes (either by a commit or a rollback). A software crash entails an implicit rollback. Consistency Only valid data (valid according Read More →

Difference between DDL, DML and DCL commands?

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE – to create objects in the database or tables ALTER – alters the structure of the database or tables DROP – delete objects from the database TRUNCATE – remove all records from a table, including all spaces allocated Read More →

Mysql Query – Set 4

Given an Employee table with following structure EmpId EmpName Department ManagerId Part 1: Write a sql to retrieve all Manager’s Name with 5 or more subordinates. Note : ManagerId is also an Employee Id. Part 2 : Write a sql to retrieve ManagerId who have 5 or more subordinates in one column and comma separated Read More →

Mysql Query – Set 3

Question: You are given a network table. Table contains 3 col’s – friend1, friend2, weight. +———-+——–+——–+—+ |friend1   | friend2  | weight | +———-+———-+——–+—+ | A         | B         | 5       | | B         | C         | Read More →