What is SQL (4 points)
- Structured Query Language (SQL) is a programming language for accessing and manipulating Relational Database Management Systems (RDBMSs).
- SQL is widely used in popular RDBMSs such as SQL Server, Oracle, and MySQL.
- The smallest unit of execution in SQL is a query. A SQL query is used to select, update, and delete data.
- In RDBMSs, all the data is stored in tables, with each table consisting of rows and columns.
Write a Create Table statement in SQL Server?
What is RDBMS and its characteristics?
RDBMS is referred to as Relation Database Management Systems (RDBMS). RDBMS possesses the following characteristics:
- Write-intensive operations suit RDBMS.
- RDBMS is good for transactional data.
- Data in flux or historical data- The RDBMS is designed to handle frequently changing data and n also store vast amounts of historical data, which can later be analyzed or “mined”.
- Application-specific schema- The RDBMS is configured per application, and a unique schema exists to support each application.
- Complex data models- The relational nature of the RDBMS makes it suitable for handling sophisticated, complex data models that require many tables, foreign key values, complex join operations, and so on.
- Data integrity- The RDBMS features many components designed to ensure data integrity. This includes rollback operations, referential integrity, and transaction-oriented operations.
- Also worth noting that SQL is traditionally designed to be storage efficient. This was especially valuable when storage cost a premium. Today Computation is expensive and storage cheap, which gives no-sql an edge.
What is the database engine in SQL Server?
- The SQL Server Database Engine, SQL Server Agent, and several other SQL Server components run as services.
- The SQL Server Database Engine is the core component of SQL Server that stores, processes, and secures data.
- It consists of a relational engine that processes queries and a storage engine that manages database files, pages, indexes, etc1.
- The database objects such as stored procedures, views, and triggers are also created and executed by the Database Engine1.
What is PL/SQL?
- PL/SQL (Procedural Language for SQL) is a procedural language developed by Oracle to work with the Oracle database using procedures in SQL.
- PL/SQL program units are compiled by the Oracle Database server and stored inside the database.
- And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency.
- PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database. PL/SQL syntaxes include declarations for variables, constants, procedures, functions, conditions, and loops.
- What are the differences between SQL and PL/SQL?
- SQL (Structured Query Language) is a standard language used to manage relational databases.
- PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension for SQL. PL/SQL is a procedural language that extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL.
- T-SQL (Transact-SQL) is a proprietary procedural language used by Microsoft in SQL Server2.
- PL/SQL cannot be used in Microsoft SQL Server2. However, it is possible to connect to MS SQL server from PL/SQL Developer3.
SQL
- Only simple IF / Else statements.
- Through SQL, you can interact with the database through ADO.NET.
- In SQL, you can execute a line of code.
- It can run only on windows.
PL/SQL:
- In PL/SQL, you can execute a block of code, not a single line of code.
- It can run in UNIX, also.
- PL/SQL language includes object-oriented programming techniques such as encapsulation, function overloading, and information hiding (all but inheritance).
What is ADO.net
- Stands for ActiveX Database Objects.
- ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components.
- ADO.NET is a set of classes that expose data access services for .NET Framework programmers.
- ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational, XML, and application data.
- ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages, or Internet browsers.
- ADO.Net contains the SQLClient, OLEDBClient and ODBCClient
What is a Check in SQL?
A Check Constraint is a rule that identifies valid values for columns of data. A Check Constraint helps to enforce Domain Integrity. If the condition in a Check Constraint is not satisfied, it prevents the value from entering the database.
Create table tableName(Column1 dataType Check(expression), Column2, columnN)
create table emp(empId int check(empId >10),empName varchar(15))
What is a default in SQL?
The default constraint allows you to set a default value for the column.
When a row is created for the first time, and there is no entry specified for the column that has a default constraint on it, then the default value is stored in the column.
The default value for the column is set only when the row is created for the first time,,e and the column value is ignored on the Insert.
Note that this is not a Not Null constraint.
Modifying the column with a NULL value or even the Insert operation specifying the Null value for the column is allowed.
What is a constraint in SQL
Constraints are the rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints, and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:
- Primary Key Constraint
- Foreign Key Constraint
- Not Null Constraint
- Unique constraint
- Default Constraint
- Check Constraint
What is a Primary Key Constraint and it syntax?
- A table column with this constraint is called the key column for the table.
- This constraint helps the table to make sure that the value is not repeated and also that there are no null entries.
- Now, this column does not allow null values and duplicate values.
- A table can have only one Primary key.
- Multiple columns can participate in the primary key column. Then the uniqueness is considered among all the participant columns by combining their values.
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);multiple columns
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);What is a Not Null Constraint?
This constraint is useful to stop storing the null entries in the specified columns.
CREATE TABLE table_Name ( column1 data_type(size) NOT NULL, column2 data_type(size) NOT NULL, …. );
How do I define constraints in SQL?
Create Table My_Constraint ( IID int NOT NULL, Salary int CHECK(Salary > 5000) )
What is a database table?
Create table TableName (columnName1 datatype, columnName2 datatype )
How do you create a table in SQL?
Create table TableName (columnName1 datatype, columnName2 datatype )
OR
create table Info ( Name varchar(20), BirthDate date, Phone nvarchar(12), City varchar(20) )
How to delete a table in SQL Server?
Delete TableName
How to update a SQL Server database table using SQL?
ax Update TableName SET ColumnName = NewData where Condition Update info Set City = 'Baroda' where id = 2
What are the relationships in the SQL Server database?
- One-to-One Relationship
- Many-to-One Relationship
- Many-to-Many Relationship
One-to-Many & Many-to-One Relationship
For a One-to-Many relationship, a single column value in one table has one or more dependent column values in another table. Look at the following diagram:
Many to Many Relationship
The third table acts as a bridge between the tables that want to establish a Many-to-Many relationship. The bridge table stores the shared information between Many-to-Many relationship tables. Have a look at the following diagram:
What is the primary key of a database?
A table column with this constraint is called the key column for the table. This constraint helps the table to make sure that the value is not repeated and also that there are no null entries.
Now, this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate in the primary key.
What is a foreign key of a database?
To define the relationship between two tables (one is called the parent and the other one is the child table) connected by columns, a foreign key constraint is used. In this constraint, the values of the child table must appear in the parent table, which means that for a foreign key, one table should point to a Primary Key in another table. A table can have multiple foreign keys,s and each foreign key can have a different referenced table.
CREATE TABLE table_name ( Col1 datatype NOT NULL, Col2 datatype NOT NULL, Col3 datatype NOT NULL, CONSTRAINT FK_Column FOREIGN KEY(Col1, Col2, Col3) REFERENCES parent_table(Col1, Col2, Col3) );
Continue here at normalization https://www.c-sharpcorner.com/UploadFile/puranindia/sql-server-interview-questions/
What is ACID?
