What is SQL?
SQL (Structured Query Language) is a standardized programming language used for managing relational databases and performing various operations like creation, deletion, fetching rows and modifying on the data in them.
What are the usages of SQL?
SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases.
On the other hand, MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.
What is the difference between SQL and PL/SQL?
SQL is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. Where as, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90's. It adds procedural features of programming languages in SQL.
A primary key is a special relational database table column or combination of columns designated to uniquely identify all table records. A primary key must contain a unique value for each row of data and cannot contain null values.
A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.
What is a foreign key?
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
What is a unique key?
A Unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.
What is an Index in SQL?
Index is used to increase the performance and allow faster retrieval of records from the table. They are special lookup tables that the database search engine can use to speed up data retrieval. An index creates an entry for each value that appears in the indexed columns. Each index name must be unique in the database. An index in a database is very similar to an index in the back of a book.
Which are the different types of indexes in SQL?
GetDate()
What are the different types of joins in SQL?
Joins are used to merge two tables or retrieve data from tables. Following are the different types of Joins:
Self join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.
What is a Database Trigger?
Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table. Actually triggers are special type of stored procedures that are defined to execute automatically in place or after data modifications.
Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data. The programs are called triggers since an event, such as adding a record to a table, fires their execution.
What is the difference between BETWEEN and IN condition operators?
The BETWEEN operator is used to display rows based on a range of values. The IN condition operator is used to check for values contained in a specific set of values.
What is the difference between DELETE and TRUNCATE statement in SQL?
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
Also Read:
SQL (Structured Query Language) is a standardized programming language used for managing relational databases and performing various operations like creation, deletion, fetching rows and modifying on the data in them.
What are the usages of SQL?
- To execute queries against a database
- To retrieve data from a database
- To inserts records in a database
- To updates records in a database
- To delete records from a database
- To create new databases
- To create new tables in a database
- To create views in a database
SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases.
On the other hand, MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.
What is the difference between SQL and PL/SQL?
SQL is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. Where as, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90's. It adds procedural features of programming languages in SQL.
- SQL is Single query or command execution, PL/SQL is full programming language
- SQL comprises of data source for reports, web pages, PL/SQL comprises of application language to build, format and display report, web pages
- SQL is declarative in nature, PL/SQL is procedural in nature.
- SQL is used for Manipulating data, while PL/SQL is used for creating applications
- Data definition language (DDL): CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.
- Data manipulation language (DML): INSERT, Retrieve, UPDATE or DELETE data from the database.
- Data control language (DCL): It allows us to control access to the database. It includes two commands GRANT (grant specific user to perform specific task)and REVOKE (cancel previously denied or granted permissions).
- A table is a set of organized data. It is composed of records and fields that hold data. Tables are also called datasheets. Each table in a database holds data about a different, but related, subject.
- The data is stored in records. A record is composed of fields and contains all the data about one particular person, company, or item in a database.
- A field is part of a record and contains a single piece of data for the subject of the record.
A primary key is a special relational database table column or combination of columns designated to uniquely identify all table records. A primary key must contain a unique value for each row of data and cannot contain null values.
A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.
What is a foreign key?
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
What is a unique key?
A Unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.
What is an Index in SQL?
Index is used to increase the performance and allow faster retrieval of records from the table. They are special lookup tables that the database search engine can use to speed up data retrieval. An index creates an entry for each value that appears in the indexed columns. Each index name must be unique in the database. An index in a database is very similar to an index in the back of a book.
Which are the different types of indexes in SQL?
- Unique Index: It does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.
- Clustered Index: It is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index.
- NonClustered Index: does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non-clustered indexes.
- One table can have only one clustered index but it can have many non clustered index.
- Clustered index determines how data is stored physically in table. Actually clustered index stores data in cluster, related data is stored together so it makes simple to retrieve data.
- Reading from a clustered index is much faster than reading from non clustered index from the same table.
- Clustered index sort and store data rows in the table or view based on their key value, while non cluster have a structure separate from the data row.
GetDate()
What are the different types of joins in SQL?
Joins are used to merge two tables or retrieve data from tables. Following are the different types of Joins:
- Inner Join: returns rows when there is at least one match of rows between the tables.
- Right Join: used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table.
- Left Join: used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
- Full Join: return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
Self join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.
What is a Database Trigger?
Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table. Actually triggers are special type of stored procedures that are defined to execute automatically in place or after data modifications.
Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data. The programs are called triggers since an event, such as adding a record to a table, fires their execution.
What is the difference between BETWEEN and IN condition operators?
The BETWEEN operator is used to display rows based on a range of values. The IN condition operator is used to check for values contained in a specific set of values.
What is the difference between DELETE and TRUNCATE statement in SQL?
- DELETE is a DML command. TRUNCATE is a DDL command.
- We can use WHERE clause in DELETE command. We cannot use WHERE clause with TRUNCATE
- DELETE statement is used to delete a row from a table. TRUNCATE statement is used to remove all the rows from a table.
- DELETE is slower than TRUNCATE statement. TRUNCATE statement is faster than DELETE statement.
- We can rollback data after using DELETE statement. It is not possible to rollback after using TRUNCATE statement.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
- Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.
- Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.
- Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.
- Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.
Also Read:
No comments:
Post a Comment