SQL, Oracle and MySQL

SQL - Distinct

Author: Shameer

•10:37 AM

SQL - DISTINCT

The DISTINCT keyword can be used to return only distinct (different) values.

Main Table

1. SELECT DISTINCT Customer_Name
FROM customer

2. SELECT DISTINCT Customer_Name FROM customer WHERE Cus_Id = 5

3. SELECT DISTINCT Customer_Name
FROM customer
ORDER BY Customer_Name

4. SELECT DISTINCT Customer_Name
FROM customer
ORDER BY Customer_Name DESC

5. SELECT DISTINCT Customer_Name, Order_Date
FROM customer

6. SELECT DISTINCT Customer_Name, Order_Date
FROM customer
WHERE Cus_Id = 4

7. SELECT DISTINCT Customer_Name, Order_Date
FROM customer
ORDER BY Customer_Name, Order_Date

8. SELECT DISTINCT Customer_Name, Order_Date
FROM customer
ORDER BY Customer_Name, Order_Date DESC

9. SELECT DISTINCT Customer_Name, Order_Date
FROM customer
ORDER BY Customer_Name DESC, Order_Date DESC

SQL - Distinct | comments (22)

SQL - Group By

Author: Shameer

•7:24 AM

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Main Table

1. SELECT Customer_Name, SUM (Order_Price)
FROM customer
GROUP BY Customer_Name

2. SELECT Customer_Name, AVG (Order_Price)
FROM customer
GROUP BY Customer_Name

3. SELECT Customer_Name, SUM (Order_Price), Order_Date
FROM customer
GROUP BY Customer_Name, Order_Date

4. SELECT Customer_Name, SUM (Order_Price) , Order_Date
   FROM customer
   WHERE Cus_Id=3
   GROUP BY Customer_Name, Order_Date

5. SELECT Customer_Name, SUM (Order_Price)
   FROM customer
   GROUP BY Customer_Name, Order_Date
   ORDER BY Customer_Name DESC

SQL - Group By | comments (1)

MySQL - How to Install

Author: Shameer

•11:38 AM

1. Download the current version of MySQL from http://dev.mysql.com. The Windows Essentials (x86) or Windows (x86) version can be used.

2. Double click setup.exe to launch the installer.

3. At the Welcome screen of the Setup Wizardclick Next

4. Choose the Typical option as the setup type and click Next.

5. Click Install

6. At this point you are prompted to create an account on the MySQL.com website. If you would like to do so, choose the appropriate option and follow the instructions.

Otherwise, choose the Skip Sign-up option and click Next.

7. The MySQL Server Database Engine is now installed. At this point the server must be configured, choose the Configure the MySQL Server now option and click Finish.

8. At the Welcome screen of the MySQL Server Instance Configuration Wizard, click Next.

9. Choose the Detailed Configuration option and click Next.

10. Choose the Server Machine option and click Next.

11. Choose the database usage type that best describes your installation. If you are unsure, choose the Multifunctional Database option and click Next.

12. Choose a location that will house the database tablespace and click Next. If the current machine will be used to store the Portfolio catalog, the default location will work fine.

13. This step of the Wizard allows you to help optimize the database for the number of concurrent connections that you expect to have to a Portfolio catalog.

If you are not sure how many concurrent users you will have, choose the Online Transaction Processing (OLTP) option and click Next.

14. Choose the Enable TCP/IP Networking option and click Next.

15. Choose the Best Support for Multilingualism option and click Next.

16. Extensis recommends running the MySQL server as a service. To do so, check the Install as a Windows Service option and choose a Service Name. It is also recommended to launch the MySQL Service automatically, choose the appropriate option and click Next.

17. In this step you choose enable the root user and choose a password. Check the Modify Security Settings option, then enter and confirm the new root user password. When finished, click Next.

18. Click Execute to configure the MySQL instance.

19. Click Finish to close the Wizard.

MySQL - How to Install | comments (0)

SQL Interview Questions

Author: Shameer

•10:09 AM

1. What is a Primary Key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications

2. What is a Foreign Key?

A foreign key is a single column or a multiple columns defined to have values that can be mapped to a primary key in another table.

3. What is an Index?

An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

4. What are NULL values?

NULL represents no value

NULL is not the same as an empty string ‘’

NULL is not same as zero value (0)

NULL can be used as any data type

NULL should not be used in any comparison operators

NULL has its own equality operator IS and not-equality operator IS NOT

5. Write a query to find the 5^th details from a table

   SELECT column_name FROM table_name WHERE auto_incremented_id =
   (SELECT TOP 1 auto_incremented_id FROM
   (SELECT TOP 3 auto_incremented_id FROM employee ORDER BY auto_incremented_id ASC)
   table_name ORDER BY auto_incremented_id DESC);

6. Write a Query to display the Top N rows ?

MySQL

SELECT column_name FROM table_name

LIMIT number

SQL Server

SELECT TOP number/percent column_name FROM table_name

Oracle

SELECT column_name FROM table_name

WHERE ROWNUM<= number

7. Write a Query to display the Last N rows ?

MySQL

SELECT column_name FROM table_name

ORDER BY auto_incremented_id DESC

LIMIT number

SQL Server

SELECT TOP number/percent column_name

FROM table_name

ORDER BY auto_incremented_id DESC

Note: The above query will display the result in descending order

To display the result in ascending order

SELECT * FROM (SELECT TOP number/percent column_name

FROM table_name

ORDER BY auto_incremented_id DESC)

ORDER BY auto_incremented_id ASC

ORACLE

SELECT * FROM (SELECT column_name FROM table_name

ORDER BY auto_incremented_id DESC)

WHERE ROWNUM<= number

ORDER BY auto_incremented_id ASC

8. Write a Query to display the second highest salary ?

SELECT MAX (empsal) FROM employee
WHERE empsal < (
SELECT MAX (empsal) FROM employee)

Explanation:

First the subquery will be executed. i.e. SELECT MAX (empsal) FROM employee) = 20000

Then the main query will be executed i.e.

SELECT MAX (empsal) FROM employee
WHERE empsal < 20000 = 15000

9. Write a query to retrieve the duplicate rows in a table ?

SELECT empname, empsal FROM employee
GROUP BY empname, empsal
HAVING count(*) > 1

10. What is RDBMS?

Relational Data Base Management Systems (RDBMS) that maintains data records and indices in tables. Relationships may be created and maintained across and among the data and tables.

11. What is SQL?

SQL stands for 'Structured Query Language', developed by IBM in 1970’s

12. What is SELECT statement?

The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.

13. What is normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases.

14. What is the INSERT statement?

The INSERT statement allows us to insert information into a database.

15. How do you delete a record from a database?

Using the DELETE statement we can remove records or any particular column values from a database.

16. How to find the total number of records in a database table?

We can use the COUNT keyword for counting records,
Example: SELECT COUNT(*) FROM Emp

17. What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows.

18. What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently.

19. What is GROUP BY?

GROUP BY keyword has been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.

20. What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?

Dropping:  (Table structure + Data are deleted), Invalidates the dependent objects, Drops the indexes

Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete

Delete: (Data alone deleted), Doesn’t perform automatic commit

21. What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

22. What are triggers? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

23. What is a join and explain different types of joins?

Join is used in queries to explain how different tables are related. Joins also let us select data from a table depending upon data from another table.

Types of joins:

INNER JOINs, OUTER JOINs, CROSS JOINs.

Note: OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

24. What is a self join?

Self join is just like any other join, except that two instances of the same table will be joined in the query.

25. How to implement one-to-one, one-to-many and

many-to-many relationships while designing tables? One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

26. What are the Properties of Sub-Query?

A sub query must be enclosed in the parenthesis.

A sub query must be put in the right hand of the comparison operator, and

A sub query cannot contain a ORDER-BY clause.

A query can contain more than one sub-queries.

27. What are types of sub-queries?

Single-row sub query, where the sub query returns only one row.

Multiple-row sub query, where the sub query returns multiple rows,.

Multiple column sub query, where the sub query returns multiple columns.

28. What are the different index configurations a table can have?

A table can have one of the following index configurations:

a) No indexes

b) A clustered index

c) A clustered index and many nonclustered indexes

d) A nonclustered index

e) Many nonclustered indexes

29. What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

SQL Interview Questions | comments (4)

SQL Join

Author: Shameer

•10:28 AM

JOIN
The SQL JOIN clause is used whenever we have to select data from 2 or more tables. There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't specify INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short ‘INNER JOIN’ = ‘JOIN’. Different databases have different syntax for their JOIN clauses).

INNER JOIN
The INNER JOIN keyword return rows when there is at least one match in both tables.

SYNTAX

SELECT column_name FROM table_name_1
INNER JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example
Table 1: Department

Table 2: Student

OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
JOIN Student
ON Department.Dept_Id = Student.Dept_Id

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN (LEFT JOIN) and RIGHT OUTER JOIN (RIGHT JOIN).

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name_1), even if there are no matches in the right table (table_name_2).

SYNTAX

SELECT column_name FROM table_name_1

LEFT JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example
Table 1: Department

Table 2: Student

OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
LEFT JOIN Student
ON Department.Dept_Id = Student.Dept_Id

RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table_name_2), even if there are no matches in the left table (table_name_1).

SYNTAX

SELECT column_name FROM table_name_1
RIGHT JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example

Table 1: Department

Table 2: Student

OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
RIGHT JOIN Student
ON Department.Dept_Id = Student.Dept_Id

FULL JOIN
The FULL JOIN returns all the rows from the left table, and all the rows from the right table.

SYNTAX

SELECT column_name FROM table_name_1
FULL JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example

Table 1: Department

Table 2: Student

OUTPUT

SELECT Department.Dept_name, Student.Stud_name
FROM Department
FULL JOIN Student
ON Department.Dept_Id = Student.Dept_Id

SQL Joins | comments (3)

Constraints

Author: Shameer

•10:31 AM

Definition:
A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

Categories of Data Integrity
The following are the categories of the data integrity:

Entity Integrity
This rule states that every table must have a Primary Key and the column chosen to be primary key should be unique and not null. It ensures that there are no duplicate rows in a table.

Domain Integrity
It restricts the date to predefined data type, the format, or the range of possible values.

Referential integrity
Ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital). i.e. any field in a table that is declared as foreign key can contain only values from a parent table’s primary key

User-Defined Integrity
Enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

SQL Constraints | comments (1)

Difference between Drop, Truncate and Delete

Author: Shameer

•10:22 AM

DELETE

1. DELETE is a DML statement.
2. DELETE removes some rows if WHERE clause is used
3. Can be rolled back
4. Can be used with or without WHERE clause
5. Does not reset identity of the table
6. Triggers will be fired.
7. When DELETE operation is performed, all the data get copied into Rollback Tablespace first,
and then delete operation get performed. Hence we can get back the data by ROLLBACK command.

SYNTAX:

To delete a particular row

DELETE FROM table_name
WHERE column_name = column_value

To delete all rows

DELETE FROM table_name
Or
DELETE * FROM table_name

DROP

1. DROP is a DDL statement.
2. Removes a table from the database. Table structures, indexes, privileges, constraints will
also be removed.
3. Cannot be rolled back
4. No Triggers will be fired.

SYNTAX:

DROP TABLE table_name

TRUNCATE

1. TRUNCATE is a DDL Statement.
2. Removes all rows from a table, but the table structures and its columns, constraints, indexes
remains.
3. Cannot be rolled back
4. Resets the identity of the table
5. Truncate is faster and uses fewer system and transaction log than delete.
6. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
7. No Triggers will be fired.
8. Cannot use WHERE conditions

SYNTAX:

TRUNCATE TABLE table_name

Difference between Drop, Truncate and Delete | comments (21)

Visitor

Labels

Blog Archive