Last Updated on February 24, 2020 by Skillslelo
What is SQL?
SQL stands for Structured Query Language. SQL is a computer language that is used to perform operations on the records stored in the database such as updating records, deleting records, creating and modifying tables, views, etc.
SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language.
What are the advantages of SQL−
- It allows users to access data in the relational database management systems.
- It allows users to describe the data.
- It allows users to define the data in a database and manipulate that data.
- It Allows embedding within other languages using SQL modules, libraries & pre-compilers.
- It allows users to create and drop databases and tables.
- It allows users to create view, stored procedure, functions in a database.
- It allows users to set permissions on tables, procedures, and views.
What are DDL, DML, and DCL?
SQL interacts with databases through its commands. These commands can be classified into the following groups based on their nature –
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
DDL – Data Definition Language
Data Definition Language consists of those SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.
CREATE
Creates a new table, a view of a table, or other object in the database.
ALTER
Modifies an existing database object, such as a table.
DROP
Deletes an entire table, a view of a table or other objects in the database.
DML – Data Manipulation Language
There are four things that you want to do with data:
» Store the data in a structured way that makes it easily retrievable.
» Change the data that is stored.
» Selectively retrieve information that responds to a need that you currently have.
» Remove data from the database that is no longer needed.
SQL statements that are part of the DML enable you to do all these things The SQL commands that deals with the manipulation of data present in database.
SELECT
Retrieves certain records from one or more tables.
INSERT
Creates a record.
UPDATE
Modifies records.
DELETE
Deletes records.
DCL – Data Control Language
The Data Control Language consists of statements that protect your precious data from misuse, misappropriation, corruption, and destruction.
DCL includes those commands which deal with the rights, permissions and other controls of the database system:
GRANT
Gives a privilege to the user.
REVOKE
Takes back privileges granted from user.
What is Database?
A database is a collection of data, stored in a logical and structured manner. In other words, it is the data, but organized in a structured way. Most databases contain multiple tables, which may each include several different fields.
For example, a company database may include tables for products, employees, and financial records. Each of these tables would have different fields that are relevant to the information stored in the table.
Table: A table is basically a collection of related data entries and it consists of numerous columns and rows.
Field: A field is a column in a table that is designed to maintain specific information about every record in the table. Every field has some heading.
Record: Each row of a table is called a record. It is a horizontal entity in a table.
What is Primary Key?
The primary key refers to the required field in the table that uniquely identifies a record.
For example, in a college registration database, your Student ID number identifies you as a unique individual—every student has a student number and no other student at the college has your exact student number.
What is Foreign Key?
The foreign key is the field in the related table used to connect to the primary key in another
table.
What is the Composite key?
Sometimes no single column uniquely identifies every row in a table, but a combination of two or more columns does.
Together, those columns comprise a composite key, which can collectively serve as a table’s primary key.
What is RDBMS?
RDBMS stands for Relational Database Management. System. A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database.
Most relational database management systems use the SQL language to access the database.
RDBMS is the basis for SQL, and for all modern database systems like;
- MS SQL Server,
- IBM DB2,
- Oracle,
- MySQL,
- Microsoft Access.
What is the schema in a database?
Relational database applications typically use multiple tables. As a database grows to support multiple applications, it becomes more and more likely that an application developer will try to give one of her tables the same name as a table that already exists in the database.
This can cause problems and frustration. To get around this problem, SQL has a hierarchical namespace structure.
A developer can define her tables as being members of a schema. A schema is now a distinct namespace that exists independently of the database user who created it.
In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable
What is join and its type?
A join matches up two tables around chosen columns that contain the same data. Then you can analyze data from both tables.
There are different types of joins available in SQL −
- INNER JOIN − returns rows when there is a match in both tables.
- LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.
- RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.
- FULL JOIN − returns rows when there is a match in one of the tables.
- SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
- CARTESIAN JOIN − returns the Cartesian product of the sets of records from the two or more joined tables.
INNER JOIN
The most important and frequently used of the joins is the INNER JOIN. An INNER JOIN only returns data where the values in the chosen columns match up exactly in both tables. You choose columns with the ON/AND keywords and a condition.
Syntax:
The basic syntax of the INNER JOIN is as follows.
SELECT table1.column1, table2.column2…
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
These joins are similar to Inner Joins except for one thing: they return all results that appear in one table and whatever matches up from the other.
Cross Join
Any multi-table query is a type of JOIN. The source tables are joined in the sense that the result table includes information taken from all the source tables.
The simplest JOIN is a two-table SELECT that has no WHERE clause qualifiers. Every row of the first table is joined to every row of the second table. The result table is referred to as the Cartesian product of the two source tables — the direct product of the two sets. (The less fancy name for the same thing is cross join.)
The number of rows in the result table is equal to the number of rows in the first source table multiplied by the number of rows in the second source table.
LEFT OUTER JOIN
A LEFT OUTER JOIN will include:
All rows from the table listed after FROM Rows that match up in the table listed after JOIN.
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.
This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
RIGHT OUTER JOIN
A RIGHT OUTER JOIN will include: All rows from the table listed after JOIN Rows that match up in the table listed after FROM
The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.
This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table.
FULL JOIN:
The SQL FULL JOIN combines the results of both left and right outer joins.
The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
A FULL JOIN allows you to combine all results from both tables in your query.
Self Join:
A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY.
It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
What is normalization?
Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process −
- Eliminating redundant data, for example, storing the same data in more than one table.
- Ensuring data dependencies make sense.
Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored.
Normalization consists of a series of guidelines that help guide you in creating a good database structure.
Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out.
The aim of normal forms is to organize the database structure so that it complies with the rules of the first normal form, then second normal form and finally the third normal form.
It is your choice to take it further and go to the fourth normal form, fifth normal form and so on, but in general, the third normal form is more than enough.
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
What is index and its type?
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.
An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.
Single-Column Indexes
A single-column index is created based on only one table column. The basic syntax is as follows.
CREATE INDEX index_name
ON table_name (column_name);
Unique Indexes
Unique indexes are used not only for performance but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.
The basic syntax is as follows.
CREATE UNIQUE INDEX index_name
on table_name (column_name);
Composite Indexes
A composite index is an index on two or more columns of a table. Its basic syntax is as follows.
CREATE INDEX index_name
on table_name (column1, column2);
What is the relationship and its type?
A relationship is an association that you establish between two tables based on common fields.
After the relationship is established, you can create a query, form, or report that displays information from more than one related table.
There are three kinds of binary (degree-two) relationships:
One-to-one (1:1) relationship:
Relates one instance of one entity class (a group of entities with common characteristics) to one instance of a second entity class.
A one-to-one relationship exists between two tables when a record in one table is related to only one record in a second table.
a one-to-one relationship between a person and that person’s driver’s license. A person can have one and only one driver’s license,
One-to-many (1:N) relationship:
Relates one instance of one entity class to multiple instances of a second entity class.
a one-to-many relationship between the PERSON entity class and the traffic violation TICKET entity class. A person can be served with multiple tickets, but a ticket can apply to one and only one person.
Many-to-many (N:M) relationship:
Relates multiple instances of one entity class to multiple instances of a second entity class.
a many-to-many relationship between the STUDENT entity class and the COURSE entity class,
What is query and its type?
The word query means to ask a question. A query is a database object that retrieves specific data from one or more database objects— either tables or other queries—and then, in a single datasheet, displays only the data that you specify when you design the query.
What is Subquery?
The subquery is a query within a query. You can use it in a JOIN, a step in transforming data, or in a condition. This is accomplished by isolating a query inside parentheses ( ) and using it as part of a greater query.
Correlated Subquery
A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.
A correlated subquery first finds the table and row specified by the enclosing statement, and then executes the subquery on the row in the subquery’s table that correlates with the current row of the enclosing statement’s table.
What is Stored Procedure?
Stored Procedure is a set of SQL statements stored within a database server and is executed as single entity.
Using stored procedures has several advantages over using inline SQL statements, like improved performance and separation of the application logic layer from database layer in n-tier applications.
What is collation?
A collation is a set of rules that determines how strings in a character set compare. A character set has a default collation sequence that defines the order in which elements are sorted.
What is a NULL value?
A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
A field with a NULL value is the one that has been left blank during a record creation.
What is the view and its advantages?
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.
Although the most fundamental constituent of a relational database is undoubtedly the table, another important concept is that of the virtual table or view.
Unlike an ordinary table, a view has no physical existence until it is called upon in a query. There is no place on disk where the rows in the view are stored. The view exists only in the metadata as a definition.
The definition describes how to pull data from tables and present it to the user in the form of a view.
A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.
Views, which are a type of virtual tables allow users to do the following −
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables that can be used to generate reports.