DDL OPERATIONS: The various DDL commands are CREATE, ALTER and DROP. Let us learn about each DDL Command one by one with examples. CREATE Statement: The CREATE statement in SQL is used to create the database and the database objects like Tables, Views, Sequences, etc. Let us see how a database and table are created in SQL. Creating a database in SQL: Syntax: CREATE DATABASE DatabaseName; Example: CREATE DATABASE Demos; On executing this query, a database named "Demos" is created. Creating Table in SQL: Database is a collection of related tables and other objects. This implies that a table is a part of the database. Now, we have created the "Demos" database. And, we need to create a "Students" table in this database. By default, the master database is selected. Select Demos database from the dropdown list or alternatively you can use the following command: Use Demos; This ...
Popular posts from this blog
Types of keys in DBMS What is Key? Keys are fields in a table which participate in below activities in RDBMS systems: To create relationships between two tables. To maintain uniqueness in a table. To keep consistent and valid data in database. Might help in fast data retrieval by facilitating indexes on column(s). SQL Server supports various types of keys, which are listed below: Candidate Key Primary Key Unique Key Alternate Key Composite Key Super Key Foreign Key Before discussing each type in brief, have a look on the below image used as an an example to define types of keys. Lets discuss each type in detail: Candidate Key Candidate key is a key of a table which can be selected as a primary key of the table. A table can have multiple candidate keys, out of which one can be selected as a primary key. Example : Employee_Id, License_Number and Passport_Number are candidate keys Primary Key Primary key is a candidate key of the table selected to...
COMMON TABLE EXPRESSIONS(cte): It is used best used to extract the hierarchical data and it is best explained with an example: Problem Many organizations have some type of hierarchy for business processes. When it comes to large organizations, the hierarchy can get very complex and large, so building a hierarchy in a RDBMS is a tedious task. We have to create views, cursors and so on, but using a CTE in SQL Server is a better solution to retrieve hierarchy-based data. Solution Common Table Expressions (CTE) have two types, recursive and non-recursive. We will see how the recursive CTE works with examples. A recursive CTE can be explained in three parts: Anchor Query: This is the first statement which is executed. This query will give the base data for the CTE. Separator: This is the middle part where in we generally use a UNION ALL and few more operators. Recursive Query: This is the main part, this is the CTE query which refers to the same CTE by recursion. ...
Comments
Post a Comment