My SQL Interview IPM Question

multiple unique, foreign, and/or primary keys?
Difference between TRUNCATE, DELETE and DROP commands
Different between Where and Having clause.
Groupby/union /orderd by clause
Different between Function and Procedure with syntax.
SQL Joins (Left outer join , Right outer join, inner join).
MySQL CONSTRAINT
Trigger and Types of trigger?
transaction./acide
Cursor and Ref cursor.
delet duplicat record from table
How to find duplicate record in db/table /row
Find duplicate data in MySQL
Select max salary
Select 3^rd highest salary

SQL Select SQL Distinct SQL Where SQL And & Or SQL Order By SQL Insert Into SQL Update SQL Delete SQL Injection SQL Select Top SQL Like SQL Wildcards SQL In SQL Between SQL Aliases

SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join

SQL Functions

SQL Functions SQL Avg() SQL Count() SQL First() SQL Last() SQL Max() SQL Min() SQL Sum() SQL Group By SQL Having SQL Ucase() SQL Lcase() SQL Mid() SQL Len() SQL Round() SQL Now() SQL Format()

What is Database?

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

MySQL Database: · MySQL is released under an open-source license. So you have nothing to pay to use it.

· MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.

· MySQL uses a standard form of the well-known SQL data language.

· MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.

· MySQL works very quickly and works well even with large data sets.

· MySQL is very friendly to PHP, the most appreciated language for web development.

Relational DataBase Management System (RDBMS) is a software that:

· Enables you to implement a database with tables, columns and indexes.

· Guarantees the Referential Integrity between rows of various tables.

· Updates the indexes automatically.

· Interprets an SQL query and combines information from various tables.

RDBMS Terminology:

Before we proceed to explain MySQL database system, let's revise few definitions related to database.

· Database: A database is a collection of tables, with related data.

· Table: A table is a matrix with data. A table in a database looks like a simple spreadsheet.

· Column: One column (data element) contains data of one and the same kind, for example the column postcode.

· Row: A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.

· Redundancy: Storing data twice, redundantly to make the system faster.

MySQL CONSTRAINT

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce integrity of database.

MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

MySQL CONSTRAINTS can be classified into two types - column level and table level.

The column level constraints can apply only to one column where as table level constraints are applied to the entire table.

MySQL CONSTRAINT are declared at the time of creating a table.

MySQL CONSTRAINTs are :

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

CONSTRAINT	DESCRIPTION
NOT NULL	In Mysql NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE	The UNIQUE constraint in Mysql does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY	A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint create a unique index for accessing the table faster.
FOREIGN KEY	A FOREIGN KEY in mysql creates a link between two tables by one specific column of both table. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK	A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT	In a Mysql table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

Primary Key: The PRIMARY KEY uniquely identifies each record in a database table.

Primary keys must contain UNIQUE values.
A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary

Can a table have multiple unique, foreign, and/or primary keys?

A table can have multiple unique and foreign keys. However, a table can have only one primary key.

Can a unique key have NULL values? Can a primary key have NULL values?

Unique key columns are allowed to hold NULL values. The values in a primary key column, however, can never be NULL.

Can a foreign key reference a non-primary key?

Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.

Can a foreign key contain null values?

Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.

Some other differences between foreign, primary, and unique keys

While unique and primary keys both enforce uniqueness on the column(s) of one table, foreign keys define a relationship between two tables. A foreign key identifies a column or group of columns in one (referencing) table that refers to a column or group of columns in another (referenced) table – in our example above, the Employee table is the referenced table and the Employee Salary table is the referencing table.

As we stated earlier, both unique and primary keys can be referenced by foreign keys.

CREATE DATABASE bookinfo;
SELECT DATABASE();

SELECT * FROM table_name;

SELECT CustomerName,City FROM Customers;

SELECT DISTINCT City FROM Customers;

The SQL INSERT INTO Statement

MySQL INSERT statement is used to insert record(s) or row(s) into a table. The insertion of records or rows in the table can be done in two ways, insert a single row at a time, and inserting multiple rows at a time.

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

The SQL UPDATE Statement

The UPDATE statement is used to update records in a table.

The UPDATE statement is used to update existing records in a table.

Notice the WHERE clause in the SQL UPDATE statement!
The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

SQL UPDATE Example

Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.
We use the following SQL statement:

Example

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';

Update Warning!

Be careful when updating records. If we had omitted the WHERE clause, in the example above, like this:

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg';

The SQL DELETE Statement

The DELETE statement is used to delete records in a table.

The DELETE statement is used to delete rows in a table.

SQL DELETE Example

Assume we wish to delete the customer "Alfreds Futterkiste" from the "Customers" table.
We use the following SQL statement:

Example

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

Delete All Data

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

or

DELETE * FROM table_name;

Note: Be very careful when deleting records. You cannot undo this statement!

SQL DROP INDEX, DROP TABLE, and DROP DATABASE

The DROP DATABASE statement is used to delete a database.

DROP DATABASE database_name

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name

1. Difference between TRUNCATE, DELETE and DROP commands

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

19) What is the difference between DELETE and TRUNCATE statement in SQL?

The main differences between SQL DELETE and TRUNCATE statements are given below:

No.	DELETE	TRUNCATE
1)	DELETE is a DML command.	TRUNCATE is a DDL command.
2)	We can use WHERE clause in DELETE command.	We cannot use WHERE clause with TRUNCATE
3)	DELETE statement is used to delete a row from a table	TRUNCATE statement is used to remove all the rows from a table.
4)	DELETE is slower than TRUNCATE statement.	TRUNCATE statement is faster than DELETE statement.
5)	You can rollback data after using DELETE statement.	It is not possible to rollback after using TRUNCATE statement.

Difference between TRUNCATE and DELETE commands

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

MySQL ALTER TABLE

The ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, rename columns or the table itself. It can also be used to change the comment for the table and type of the table.

ALTER TABLE testtable RENAME w3r1;

To change column col1 from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from VARCHAR(15) to CHAR(25) as well as renaming it from col2 to col3, the following statement can be used.

ALTER TABLE w3r1 MODIFY col1 TINYINT NOT NULL, CHANGE col2 col3 VARCHAR(25);

To add a new TIMESTAMP column named col4, the following statement can be used.

ALTER TABLE w3r1 ADD col4 TIMESTAMP;

he ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The SQL WHERE Clause The WHERE clause is used to filter records

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SELECT * FROM Customers
WHERE Country='Mexico';

SELECT * FROM Customers
WHERE CustomerID=1;

. SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

MySQL HAVING clause

The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for group of rows or aggregates.
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.

Now, we can find which order has total sales greater than $1000. We use the MySQL HAVING clause on the aggregate as follows:

SELECT

ordernumber,

SUM(quantityOrdered) AS itemsCount,

SUM(priceeach) AS total

FROM

orderdetails

GROUP BY ordernumber

HAVING total > 1000;

1. Different between Where and Having clause.

Here is The Difference

Though the HAVING clause specifies a condition that is

similar to the purpose of a WHERE clause, the two clauses

are not interchangeable. Listed below are some differences

to help distinguish between the two:

1. The WHERE clause specifies the criteria which individual

records must meet to be selcted by a query. It can be used

without the GROUP BY clause. The HAVING clause cannot be

used without the GROUP BY clause.

2. The WHERE clause selects rows before grouping. The

HAVING clause selects rows after grouping.

3. The WHERE clause cannot contain aggregate functions. The

HAVING clause can contain aggregate functions.

The HAVING clause allows you to filter the results of

aggregate functions,

such as COUNT() or AVG() or SUM(), or MAX() or MIN(), just

to name a few.

HAVING provides you a means to filter these results in the

same query,

as opposed to saving the results of a WHERE clause SQL

statement to a temporary table

and running another query on the temporary table results to

extract the same results.

WHERE is used to filter rows. HAVING is usually used to

filter rows after performing an aggregation.

One limitation when you use the HAVING clause as compare

to WHERE clause.

-- Having clause only supports the Grouped Columns &

Aggregation filter..

--Where there is a column level filter then always use the

Where clause,

--Use Having clause only for Aggregation filter.

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set.

The ORDER BY keyword is used to sort the result-set by one or more columns.

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

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

MySQL UNION

Description

In MySQL the UNION operator is used to combine the result from multiple SELECT statements into a single result set.
The default characteristic of UNION is, to remove the duplicate rows from the result. The DISTINCT keyword which is optional does not make any effect, because, by default, it specifies duplicate-row removal. But if we use the optional keyword ALL, the duplicate-row removal does not happened and the result set includes all matching rows from all the SELECT statements.

The UNION operator returns result from both queries after eliminating the duplicate rows.

Example

If we want to display the present and previous details of jobs of all employees once the following mysql statement can be used.

view plain copy to clipboard print ?

Select employee_id, job_id
FROM employees
UNION
Select employee_id,job_id
FROM job_history;

MySQL UNION ALL

The UNION ALL operator does not eliminate duplicate selected rows and returns all rows.

Pictorial presentation of UNION ALL operator

The UNION ALL operator returns all the rows from both the queries and no duplication elimination happens..

MySQL UNION vs UNION ALL

In MySQL the UNION operator returns the combine result from multiple SELECT statements into a single result set but exclude the duplicate rows where as the UNION ALL operator avoids the elimination of duplicate selected rows and returns all rows.

Example

If we want to display the present and previous details of jobs of all employees, and they may appear more than once, the following mysql statement can be used.

view plain copy to clipboard print ?

Select employee_id, job_id,department_id
FROM employees
UNION ALL
Select employee_id,job_id,department_id
FROM job_history;

MySQL UNION DISTINCT

The DISTINCT clause with UNION produced nothing extra as the simple UNION done. From the UNION operator, we know that all rows will be displayed from both the queries except the duplicate are once.

f we want to display the present and previous details of jobs of all employees once the following mysql statement can be used.

view plain copy to clipboard print ?

SELECT DISTINCT employee_id, job_id
FROM employees
UNION
SELECT DISTINCT employee_id,job_id
FROM job_history;

The SQL SELECT TOP Clause

The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the SELECT TOP clause.

What is the difference between stored procedures and functions.

1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We pass some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

2. A stored procedure is a program (or procedure) which is
physically stored within a database. They are usually written in
a proprietary database language like PL/SQL for Oracle database
or PL/PgSQL for PostgreSQL. The advantage of a stored procedure
is that when it is run, in response to a user request, it is run
directly by the database engine, which usually runs on a separate
database server. As such, it has direct access to the data it
needs to manipulate and only needs to send its results back to
the user, doing away with the overhead of communicating large
amounts of data back and forth.
6>We can go for transaction management in procedure whereas we can't go in function.
7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

The differences between stored procedures and functions are given below:

Stored Procedure	Function
is used to perform business logic.	is used to perform calculation.
must not have the return type.	must have the return type.
may return 0 or more values.	may return only one values.
We can call functions from the procedure.	Procedure cannot be called from function.
Procedure supports input and output parameters.	Function supports only input parameter.
Exception handling using try/catch block can be used in stored procedures.	Exception handling using try/catch can't be used in user defined functions.

Triggers

A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

MySQL Trigger : Example AFTER INSERT

MySQL Trigger : Example BEFORE INSERT

MySQL Trigger : Example AFTER UPDATE

MySQL Trigger : Example BEFORE UPDATE

MySQL Trigger : Example AFTER DELETE

Uses for triggers :

Enforce business rules
Validate input data
Generate a unique value for a newly inserted row on a different file.
Write to other files for audit trail purposes
Query from other files for cross-referencing purposes
Access system functions
Replicate data to different files to achieve data consistency

Go Top

Benefits of using triggers in business :

Faster application development. Because the database stores triggers, you do not have to code the trigger actions into each database application.
Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
Improve performance in client/server environment. All rules run in the server before the result returns.

Implementation of SQL triggers is based on the SQL standard. It supports constructs that are common to most programming languages. It supports the declaration of local variables, statements to control the flow of the procedure, assignment of expression results to variables, and error handling

Joins

Types of MySQL Joins

INNER JOIN( equi join) INNER JOIN return all rows from multiple tables where the join condition is met.
OUTER JOIN :
LEFT OUTER JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT OUTER JOIN: Return all rows from the right table, and the matched rows from the left table
FULL OUTER JOIN: Return all rows when there is a match in ONE of the tables
SELFJOIN

SQL6y7y SELECT TOP Equivalent in MySQL and Oracle

Mysql Example

SELECT *
FROM Persons
LIMIT 5;

Oracle Syntax

Example

SELECT *
FROM Persons
WHERE ROWNUM <=5;

Return 5 record / row

SELECT TOP 2 * FROM Customers;

Return 5 record / row from top

SELECT TOP 50 PERCENT * FROM Customers;

Transaction

A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back..
A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL (Data Definition Language (DDL) is used to manage table and index structure and CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements) statement is issued.

Understand the concept of a transaction

To understand the concept of a transaction, consider a banking database. Suppose a bank customer transfers money from his savings account (SB a/c) to his current account (CA a/c), the statement will be divided into four blocks :

Debit SB a/c.
Credit CA a/c.
Record in Transaction Journal
End Transaction

The SQL statement to debit SB a/c is as follows :

UPDATE sb_accounts
SET balance = balance - 1000
WHERE account_no = 932656 ;

The SQL statement to credit OD a/c is as follows :

UPDATE ca_accounts
SET balance = balance + 1000
WHERE account_no = 933456 ;

The SQL statement for record in transaction journal is as follows :

INSERT INTO journal VALUES
(100896, 'Tansaction on Benjamin Hampshair a/c', '26-AUG-08' 932656, 933456, 1000);

The SQL statement for End Transaction is as follows :

COMMIT WORK;

Go Top

MySQL and the ACID Model

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In MySQL, InnoDB storage engine supports ACID-compliant features. The following sections discuss how MySQL features, in particular the InnoDB storage engine, interact with the categories of the ACID model :

Atomicity : The atomicity aspect of the ACID model mainly involves InnoDB transactions. Related MySQL features include :

Autocommit setting.
COMMIT statement.
ROLLBACK statement.
Operational data from the INFORMATION_SCHEMA tables.

Consistency : The consistency aspect of the ACID model mainly involves internal InnoDB processing to protect data from crashes. Related MySQL features include :

InnoDB doublewrite buffer.
InnoDB crash recovery.

Isolation : The isolation aspect of the ACID model mainly involves InnoDB transactions, in particular the isolation level that applies to each transaction. Related MySQL features include :

Autocommit setting.
SET ISOLATION LEVEL statement.
The low-level details of InnoDB locking. During performance tuning, you see these details through INFORMATION_SCHEMA tables.

Durability : The durability aspect of the ACID model involves MySQL software features interacting with your particular hardware configuration. Because of the many possibilities depending on the capabilities of your CPU, network, and storage devices, this aspect is the most complicated to provide concrete guidelines for. Related MySQL features include:

InnoDB doublewrite buffer, turned on and off by the innodb_doublewrite configuration option.
Configuration option innodb_flush_log_at_trx_commit.
Configuration option sync_binlog.
Configuration option innodb_file_per_table.
Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
Battery-backed cache in a storage device.
The operating system used to run MySQL, in particular its support for the fsync() system call.
Uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
Your backup strategy, such as frequency and types of backups, and backup retention periods.
For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.

MySQL aggregate functions and grouping

MySQL aggregate functions :

Description

MySQL aggregate functions retrieve a single value after performing a calculation on a set of values.
In general, aggregate functions ignore null values.
Often, aggregate functions are accompanied by the GROUP BY clause of the SELECT statement.

List of MySQL aggregate functions and a hint of what they do

AVG()

MySQL AVG() retrieves the average value of the argument.
MySQL AVG() function retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL.

Example : MySQL AVG() function

The following MySQL statement will return average number of pages (of books) from the book_mast table.

view plain copy to clipboard print ?

SELECT AVG(no_page)
FROM book_mast;

BIT_AND()

MySQL BIT_AND() bitwise and.

BIT_OR()

MySQL BIT_OR() retrieves bitwise or.

BIT_XOR()

MySQL BIT_OR() retrieves bitwise xor.

COUNT(DISTINCT)

MySQL COUNT(DISTINCT) retrieves the count of a number of different values.

COUNT()

MySQL COUNT() function returns a count of number of non-NULL values of a given expression. Code

view plain copy to clipboard print ?

SELECT COUNT(*)
FROM author;

Output

mysql> SELECT COUNT(*)

-> FROM author;

+----------+

| COUNT(*) |

+----------+

| 15 |

+----------+

1 row in set (0.00 sec)

If it does not find any matching row, it returns 0.

MySQL COUNT() retrieves a count of the number of rows returned.

GROUP_CONCAT()

MySQL GROUP_CONCAT() retrieves a concatenated string.

MAX()

MySQL MAX() retrieves the maximum value.

MIN()

MySQL BIT_OR() retrieves the minimum value.

STD()

MySQL MIN() retrieves the population standard deviation.

STDDEV_POP()

MySQL BIT_OR() retrieves the population standard deviation.

STDDEV_SAMP()

MySQL STDDEV_POP() retrieves the sample standard deviation.

STDDEV()

MySQL STDDEV() retrieves the population standard deviation.

SUM()

MySQL SUM() retrieves the sum.

VAR_POP()

MySQL VAR_POP() the population standard variance.

VAR_SAMP()

MySQL VAR_POP() the sample variance.

VARIANCE()

MySQL VAR_POP() the population standard variance.

SQL LIKE Operator

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

The SQL LIKE Operator

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

KEY

Q1. How to find duplicate record in db/table /row?

Ans :

Q2 find duplicate valu in db ?

Ans:

Q3 delet duplicat record from table.

metimes we required to remove duplicate records from a table although table has a UniqueID Column with identity. In this article, I would like to share a best way to delete duplicate records from a table in SQL Server.

Suppose we have below Employee table in SQL Server.

1. CREATE TABLE dbo.Employee

2. (

3. EmpID int IDENTITY(1,1) NOT NULL,

4. Name varchar(55) NULL,

5. Salary decimal(10, 2) NULL,

6. Designation varchar(20) NULL

7. )

The data in this table is as shown below:

Remove Duplicate Records by using ROW_NUMBER()

1. WITH TempEmp (Name,duplicateRecCount)

2. AS

3. (

4. SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name)

5. AS duplicateRecCount

6. FROM dbo.Employee

7. )

8. --Now Delete Duplicate Records

9. DELETE FROM TempEmp

10. WHERE duplicateRecCount > 1

1. --See affected table

2. Select * from Employee

For more help about ROW_NUMBER(), please follow the MSDN link.

Summary

In this article, I expose how can you remove duplicate records from a table in SQL Server. I hope after reading this article you will be able to use these tips. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Q Find duplicate record on coloum in taabale .

Find duplicate data in MySQL

<<Previous Next>>

Objective

There are many a occasions when you need to find duplicate values available in a column of a MySql table. Often, you may want to count the number of duplicate values in a MySQL table.
In this article we have discussed a query where you can find duplicates, triplicates, quadruplicates (or more) data from a MySQL table.
We have discussed how to find duplicate values with INNER JOIN and subquery, INNER JOIN and DISTINCT, and also how to count duplicate values with GROUP BY and HAVING.

Table in question

We have used a table called 'item' to apply the query :
Table Name : item
Structure : item_code varchar(20), value int(11), quantity int(11) where item_code is the primary key.

Using INNER JOIN and Subquery

Now we want to get the details of those records where quantity field have duplicate/triplicates values. In the image above, values marked with red rectangle exist more than once.

Here is the query :

view plain copy to clipboard print ?

SELECT item_code, value, item.quantity
FROM item
INNER JOIN(
SELECT quantity
FROM item
GROUP BY quantity
HAVING COUNT(item_code) >1
)temp ON item.quantity= temp.quantity;

Output

To get the above result we have used a query with an INNER JOIN (INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.) statement. INNER JOIN uses the main table 'item' and a temporary table 'temp' whose data comes from a subquery. Here is the subquery and it's output :

view plain copy to clipboard print ?

SELECT quantity
FROM item
GROUP BY quantity
HAVING COUNT(item_code) >1

Output

Now the following main query will execute on 'item' and 'temp' tables where the common field is quantity and the result will be as follows :

SELECT item_code, value, item.quantity

FROM item

INNER JOIN temp ON item.quantity= temp.quantity;

Using INNER JOIN and DISTINCT

You can use the following query to get the same result. Here we apply 'NNER JOIN the table with itself. As the same quantity value exist in more than two records, a DISTINCT clause is used.
Here is the code and the output :

view plain copy to clipboard print ?

SELECT distinct a.item_code, a.value, a.quantity
FROM item a
INNER JOIN item b ON a.quantity = b.quantity
WHERE a.item_code <> b.item_code

Output

Count duplicate data in MySQL

The following query count those records where quantity field holds duplicate/triplicates (or more) data.
Table data :

view plain copy to clipboard print ?

SELECT item_code, COUNT( quantity ) x
FROM item
GROUP BY quantity
HAVING x >1

Output

Count duplicate records in MySQL

To count the total duplicate (or more) 'quantity' of 'item' table you can use the following query :

view plain copy to clipboard print ?

SELECT count(*) AS Total_duplicate_count
FROM
(SELECT item_code FROM item
GROUP BY quantity HAVING COUNT(quantity) > 1
)AS x

Output

SQL Injection

« Previous

Next Chapter »

An SQL Injection can destroy your database.

SQL in Web Pages

In the previous chapters, you have learned to retrieve (and update) database data, using SQL.
When SQL is used to display data on a web page, it is common to let web users input their own search values.
Since SQL statements are text only, it is easy, with a little piece of computer code, to dynamically change SQL statements to provide the user with selected data:

SQL Injection

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.
Injected SQL commands can alter SQL statement and compromise the security of a web application.

What is Partitioning?

Partitioning (a database design technique) improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, therefore queries that access only a fraction of the data can run faster because there is less data to scan. There are two major forms of partitioning :

Horizontal Partitioning : Horizontal partitioning divides table rows in multiple partitions (based on a logic). All columns defined to a table are found in each partition, so no actual table attributes are missing. All the partition can be addressed individually or collectively. For example, a table that contains whole year sale transaction being partitioned horizontally into twelve distinct partitions, where each partition contains one month's data.
Vertical Partitioning : Vertical partitioning divides a table into multiple tables that contain fewer columns. Like horizontal partitioning, in vertical partitioning a query scan less data which increases query performance. For example, a table that contains a number of very wide text or BLOB columns that aren't addressed often being broken into two tables that has the most referenced columns in one table and the text or BLOB data in another.

AUTO INCREMENT Field

« Previous

Next Chapter »

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

AUTO INCREMENT a Field

Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
We would like to create an auto-increment field in a table.

Syntax for MySQL

The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

The SQL statement above would insert a new record into the "Persons" table. The "ID" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

Description

In the following section we have given a list of FUNCTIONS and OPERATORS in MySQL as any one can view the FUNCTIONS at a glance and jump immediately to his/her requirement.

MySQL Function & Operators	Description
Introduction of Mysql functions and operators	Types of MySQL functions and operators.
Mysql Comparison Functions and Operators	Some Comparison Functions and Operators are - Between and COALESCE() NULL Safe equal to operator (<=>) Equal operator(=) Greater than or equal operator(>=) Greater than operator(>) GREATEST() IN() INTERVAL() IS NOT NULL IS NOT IS NULL IS IS NULL() LEAST() LESS THAN OR EQUAL OPERATOR(<=) LESS THAN OPERATOR(<) LIKE NOT BETWEEN AND NOT EQUAL OPERATOR(<>,!=) NOT IN() NOT LIKE STRCMP()
Mysql Logical Operators	MySQL logical AND operator compares two expressions and returns true if both of the expressions are true. Some Logical Operators are - And operator Not operator Or operator Xor operator
Mysql Control Flow Functions	Some Control Flow Functions are - Case operator IF() IFNULL() NULLIF()
Mysql String Functions	Some String Functions in MySQL are - ASCII BIN BIT_LENGTH CHAR_LENGTH CHAR CHARACTER_LENGTH CONCAT_WS CONCAT ELT EXPORT_SET FIELD FIND_IN_SET FORMAT HEX INSERT INSTR LCASE LEFT LENGTH LIKE LOAD_FILE LOCATE LOWER LPAD LTRIM MAKE_SET MID NOT LIKE NOT REGEXP OCTET_LENGTH ORD POSITION QUOTE REGEXP REPEAT REPLACE REVERSE RIGHT RLIKE NOT RLIKE RPAD RTRIM SOUNDEX SOUNDS_LIKE SPACE STRCMP SUBSTR SUBSTRING_INDEX SUBSTRING TRIM UCASE UNHEX UPPER
Mysql Mathematical Functions	Some Mathematical Functions in MySQL are - ABS() ACOS() ASIN() ATAN2() ATAN() CEIL() CEILING() CONV() COS() COT() CRC32() DEGREES() DIV DIVISION EXP() FLOOR() LN() LOG() LOG2() LOG10() MOD() OCT() PI() POW() POWER() RADIANS() RAND() ROUND() SIGN() SIN() SQRT() TAN() TRUNCATE() FORMAT()
Mysql Date and Time Functions	MySQL Date and Time Functions are used in various type of date and time operation.
Mysql Encryption and Compression Functions	MySQL Encryption and Compression Functions are used to encrypt and decrypt a string. Some Encryption and Compression Functions in MySQL are - AES_DECRYPT() AES_ENCRYPT() COMPRESS() DECODE() DES_DECRYPT() DES_ENCRYPT() ENCODE() ENCRYPT() MD5() OLD_PASSWORD() PASSWORD() SHA1() UNCOMPRESS() UNCOMPRESSED_LENGTH()
Mysql Bit Functions	Some Bit Functions in MySQL are - BIT_COUNT BITWISE AND INVERT BITS BITWISE OR BITWISE XOR Left shift Right shift
Mysql Information Functions	Some Information Functions in MySQL are - BENCHMARK() CHARSET() COERCIBILITY() COLLATION() CONNECTION_ID() CURRENT_USER(), CURRENT_USER DATABASE() FOUND_ROWS() LAST_INSERT_ID() SCHEMA() SESSION_USER() SYSTEM_USER() USER() VERSION()

MySQL : Rows holding group wise maximum

<<Previous Next>>

Description

In this page we have shown how to retrieve the rows holding group wise maximum for a column.
In the example shown and explained in this page, we retrieved the most expensive books of each language.

Example :

Sample table : book_mast

Code

view plain copy to clipboard print ?

SELECT book_name, pub_lang, book_price
FROM book_mast b1
WHERE book_price =
SELECT MAX( b2.book_price )
FROM book_mast b2
WHERE b1.pub_lang = b2.pub_lang );

Explanation

The above MySQL statement has performed the following -
1. book_name, pub_lang, book_price are retrieved from book_mast, if
a) language of the most costly book of book_mast aliased as b2 and b1 are alike.

Output

mysql> SELECT book_name, pub_lang, book_price

    -> FROM book_mast b1

    -> WHERE book_price = (

    -> SELECT MAX( b2.book_price )

 3   -> FROM book_mast b2

    -> WHERE b1.pub_lang = b2.pub_lang );

+----------------------------------+----------+------------+

| book_name                        | pub_lang | book_price |

+----------------------------------+----------+------------+

| Guide to Networking              | Hindi    |     200.00 |

| Transfer  of Heat and Mass       | English  |     250.00 |

| Fundamentals of Heat             | German   |     112.00 |

| The Experimental Analysis of Cat | French   |      95.00 |

+----------------------------------+----------+------------+

4 rows in set (0.00 sec)

MySQL Date calculation

<<Previous Next>>

Description

In this page we have shown a advanced MySQL date calculation using the following functions :
CURDATE() function which returns the current date of the computer, YEAR() function which returns the year of the specified date, MONTH() function which returns the month of the specified date, DAY() function which returns the day of the specified date, RIGHT() function which returns the number of character as specified within the function from the given string or date. The part of the expression that compares the returns from RIGHT() function evaluates 1 or 0.

Example :

Sample table : publisher

Code

view plain copy to clipboard print ?

SELECT pub_name, estd, CURDATE(),
YEAR(estd),MONTH(estd),
DAY(estd),(RIGHT(CURDATE(),5)<RIGHT(estd,5)) AS ‘return’
FROM publisher;

Explanation

The above MySQL statement returns Publisher's name, Date of establishment, Current date, Year of establishment, Month of establishment, Day of establishment and return column which is calculated using RIGHT().

Output

mysql> SELECT pub_name, estd, CURDATE(),

    -> YEAR(estd),MONTH(estd),

    -> DAY(estd),(RIGHT(CURDATE(),5) FROM publisher;

+------------------------------+------------+------------+------------+-------------+-----------+--------+

| pub_name                     | estd       | CURDATE()  | YEAR(estd) | MONTH(estd) | DAY(estd) | return |

+------------------------------+------------+------------+------------+-------------+-----------+--------+

| Jex Max Publication          | 1969-12-25 | 2015-04-04 |       1969 |          12 |        25 |      1 |

| BPP Publication              | 1985-10-01 | 2015-04-04 |       1985 |          10 |         1 |      1 |

| New Harrold Publication      | 1975-09-05 | 2015-04-04 |       1975 |           9 |         5 |      1 |

| Ultra Press Inc.             | 1948-07-10 | 2015-04-04 |       1948 |           7 |        10 |      1 |

| Mountain Publication         | 1975-01-01 | 2015-04-04 |       1975 |           1 |         1 |      0 |

| Summer Night Publication     | 1990-12-10 | 2015-04-04 |       1990 |          12 |        10 |      1 |

| Pieterson Grp. of Publishers | 1950-07-15 | 2015-04-04 |       1950 |           7 |        15 |      1 |

| Novel Publisher Ltd.         | 2000-01-01 | 2015-04-04 |       2000 |           1 |         1 |      0 |

+------------------------------+------------+------------+------------+-------------+-----------+--------+

8 rows in set (0.00 sec)

The Cursor is a handle (name or a pointer) for the memory associated with a specific statement. A cursor is basically an Area allocated by Oracle for executing the Sql Statements. Oracle Uses an Implicit Cursor statement for a single row query and Explicit Cursor for a multi row query.

Types of Cursor :

I) Implicit

II) Explicit (Explicit cursor has three sub-types)

1) Simple Cursor

2) Parameterised Cursor

3) Ref Cursor

Tuesday, January 8, 2019

My SQL Interview IPM Question

My SQL Interview IPM Question

What is Database?

RDBMS Terminology:

MySQL CONSTRAINT

SQL UPDATE Example

Example

SQL DELETE Example

Example

SQL DROP INDEX, DROP TABLE, and DROP DATABASE

MySQL ALTER TABLE

he ALTER TABLE Statement

MySQL HAVING clause

MySQL UNION

Description

MySQL UNION ALL

Pictorial presentation of UNION ALL operator

MySQL UNION vs UNION ALL

The SQL SELECT TOP Clause

Uses for triggers :

Benefits of using triggers in business :

Joins

SQL6y7y SELECT TOP Equivalent in MySQL and Oracle

Mysql Example

Oracle Syntax

Example

Transaction

MySQL aggregate functions and grouping

MySQL aggregate functions :

Description

List of MySQL aggregate functions and a hint of what they do

AVG()

BIT_AND()

BIT_OR()

BIT_XOR()

COUNT(DISTINCT)

COUNT()

MySQL COUNT() function returns a count of number of non-NULL values of a given expression. Code

GROUP_CONCAT()

MAX()

MIN()

STD()

STDDEV_POP()

STDDEV_SAMP()

STDDEV()

SUM()

VAR_POP()

VAR_SAMP()

VARIANCE()

SQL LIKE Operator

The SQL LIKE Operator

Find duplicate data in MySQL

Objective

Table in question

Using INNER JOIN and Subquery

Output

Output

Using INNER JOIN and DISTINCT

Output

Count duplicate data in MySQL

Output

Count duplicate records in MySQL

Output

SQL Injection

SQL in Web Pages

SQL Injection

AUTO INCREMENT Field

AUTO INCREMENT a Field

Syntax for MySQL

MySQL : Rows holding group wise maximum

Description

Example :

Sample table : book_mast

Code

Explanation

Output

MySQL Date calculation

Description

Example :