limited time offer

MySQL CRUD Operations Interview Questions

Showing 1 - 10 of 10 results

How can you retrieve a particular number of records from a table in MySQL?

LIMIT clause is used with the SQL statement to retrieve a particular number of records from a table. From which record and how many records will be retrieved are defined by the LIMIT clause.

Example:

Get all records.

mysql> select * from shopping.customer;

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

4 rows in set (0.00 sec)

Get top 2 records.

mysql> select * from shopping.customer LIMIT 2;

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

2 rows in set (0.00 sec)

Get 2nd and 3rd record.

mysql> select * from shopping.customer LIMIT 1, 2;

+------------+--------------+------------+---------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address | CityID | PostalCode | CountryID |
+------------+--------------+------------+---------+--------+------------+-----------+
|          2 | Ram          | 9650423377 | A-487   |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485   |     11 |     110085 |        91 |
+------------+--------------+------------+---------+--------+------------+-----------+

2 rows in set (0.00 sec)

What is the difference between the Primary key and the Unique key in MySQL?

Unique data is stored in the primary key and unique key fields. The primary key field never accepts NULL value but a unique key field accepts a NULL value.

Example: Create below table

CREATE TABLE shopping.myusers(
        uid int(10) PRIMARY KEY,
        uname VARCHAR(50) UNIQUE KEY,
        password VARCHAR(50));

Now insert NULL in unique key, you won't get any error

INSERT INTO shopping.myusers(uid, uname, password) VALUES(1, NULL, '1234');

Query OK, 1 row affected (0.01 sec)

Now insert NULL in primary key, you will get error:

mysql> INSERT INTO shopping.myusers(uid, uname, password) VALUES(NULL, NULL, '1234');

ERROR 1048 (23000): Column 'uid' cannot be null

How can you add and remove any column of a table in MySQL?

The syntax for adding any column in an existing table is shown below:

ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column]

The syntax for removing any column from an existing table is shown below:

ALTER TABLE table_name DROP COLUMN column_name;

How can you filter the duplicate data while retrieving records from the table in MySQL?

A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.

Example

mysql> SELECT * FROM Shopping.Customer;
+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Sohan        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+
mysql> SELECT DISTINCT(PostalCode) FROM Shopping.Customer;
+------------+
| PostalCode |
+------------+
|     122001 |
|     110085 |
|     122002 |
+------------+
3 rows in set (0.00 sec)

What are The Different Types Of Statements Available In SQL ?

1. DML (Data Manipulation Language):

These are used to manage records in the table. It includes the basic operations carried out on the tabular data like selecting few records, inserting new ones, deleting the unnecessary ones, and updating/modifying the existing ones. Following are different DML statements available in SQL:

  • <SELECT> – retrieve data from the database
  • <INSERT> – to insert data into a table
  • <UPDATE> – it updates existing data within a table
  • <DELETE> – to delete all records from a table
  • <MERGE> – UPSERT operation (insert or update)
  • <CALL> – to call a PL/SQL or Java subprogram
  • <EXPLAIN PLAN> – define access path to data
  • <LOCK TABLE> – control concurrency

2. DDL (Data Definition Language):

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects. Following are different DDL statements available in SQL:

  • <CREATE> – to create objects in the database
  • <ALTER> – alters the structure of the database
  • <DROP> – to delete objects from the database
  • <TRUNCATE> – remove all records from a table. It also frees all the space allocated to them.
  • <COMMENT> – add comments to the data dictionary
  • <RENAME> – to rename an object.

3. DCL (Data Control Language):

DCL statements control the level of access that users have to the database objects. Following are different DCL statements available in SQL:

  • <GRANT> – it gives access privileges to the user for the database
  • <REVOKE> – to withdraw the access privileges given by GRANT command.

4. TCL (Transaction Control Language):

It allows you to control and manage transactions to maintain the integrity of data within SQL statements. Following are different TCL statements:

  • <COMMIT> – to save the work
  • <SAVEPOINT> – identify a point in a transaction to which you can rollback at a later point in time when required
  • <ROLLBACK> – restore the database to original since the last COMMIT
  • <SET TRANSACTION> – Change transaction options like isolation level and what rollback segment to use.

What Are The Clauses Available In MySQL ?

MySQL provides with the following clauses that can be used in the SELECT statements:

  • Where: The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. Only rows that meet the conditions contribute data to the result set. Data from rows that do not meet the conditions is not used.
  • Group By: SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement. The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The GROUP BY statement is used with aggregation function.
  • Having: HAVING clause is used to specify a search condition for a group or an aggregate.Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.  
  • Order By: The ORDER BY clause sorts the result-set in ascending or descending order. It sorts the records in ascending order by default. DESC keyword is used to sort the records in descending order.

What is truncate command in sql ?

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself without removing its structure. Example:

TRUNCATE TABLE sys.Members;

The SQL TRUNCATE TABLE statement is used to remove all records from a table. It performs the same function as a DELETE statement without a WHERE clause. You may not be able to roll back the TRUNCATE TABLE statement in all SQL databases.

Truncate is all or nothing, you can not use WHERE clause with Truncate. With DELETE you can use WHERE clause. Below statement will return error:

TRUNCATE TABLE sys.Members where membership_number = 2;

Truncate is faster than DELETE command.

Write an sql query to find the year from date using MySQL ?

You can use either Date_Format() or Year() function.

Date_Format() to find Year

SELECT DATE_FORMAT('2015-07-08', '%Y');
SELECT DATE_FORMAT(NOW(), '%Y');
SELECT DATE_FORMAT(CURDATE(), '%Y');

first query will return 2015 and remaining 2 will return current year.

YEAR() to find Year

SELECT YEAR('2018-07-08');
SELECT YEAR(NOW());
SELECT YEAR(CURDATE());

first query will return 2015 and remaining 2 will return current year

What is the command to fetch the first 5 characters of string in MYSQL ?

There are several ways to fetch first 5 characters of a string. Consider value of column login_id is "teacher1.demo" output will be "teach" if you executed any of below query:

Technique 1:

SELECT SUBSTRING(login_id, 1, 5) FROM user_master where id = 4013482;

Technique 2:

SELECT LEFT(login_id, 5) FROM ums_api.user_master where id = 4013482;

Syntax

SUBSTRING(string, start, length)

 

Subscribe to MySQL CRUD Operations Interview Questions