SQL stands for Structure Query Language used to maintain records in RDBMS. RDBMS is relational database management system based on relation modal; RDBMS support: sql, ms-sql, and ms-access.
In the following chapter we will take a look at the SQL Server Database that includes: Databases, Schemas, and Objects.
Database: SQL server stores data in database. Database is a collection of database object such as schema, table, view, store procedure and other objects.
Type of Database: SQL server has two type of databases.
System Database : System databases are pre-built databases.
Master: this is a system database, it record the information of existing databases location, configuration.
Model: Model database is a template database. Databases inherited from Model database, The newly created database inherit changes if you modify the Model database
Temp DB: TempDb store the information of temporary table
MS DB: It store the information that require by SQL Server Agent such as job scheduling ,alert
User Database
User database are database that created by user. In SQL Server to create a database use following query and execute
CREATE DATABASE dbname;
Example
CREATE DATABASE Stock;
Schema: A schema is a brief plan of entity, attributes, table and relationship in database. Schema is used to plan a database design. It is a container of a database object such as table, view and other required by relation database management system.
According to above figure illustrate that database contain schema and schema contain database object.
Objects: Database object are table, view, procedure and other object required by relation database system.
Getting list of database objects:
Creating database objects:
Creating table : following is a basic syntax of create table
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….
);
Example:
CREATE TABLE Sale
(
saleid int,
sellername varchar(20),
saleprice int,
)
Creating view: Following is basic syntax of view statement
CREATE VIEW view_name AS
SELECT column_name
FROM table_name
WHERE condition
Example
CREATE VIEW vw_getsale AS
SELECT *
FROM sale
WHERE 1=1
After executing above statement , you can see that SQL server has listed the database, schema and object.
SQL is a relation model of RDBMS. RDBMS based on relation model. SQL server is used to maintain the database in relational model. In brief: database is a container of object such as Table, view and other object require by relational database.
Photo by mortifi