SQL Server is one of the widely used relational database servers from Microsoft. It uses the structured query language(SQL in short) to store and retrieve the data from the database.
Using Structured Query Language we can create the databases, tables, stored procedures, functions, jobs, etc. to maintain and manipulate the relational data.
SQL Basics
What is the latest version of the SQL server and is there any free version to use?
SQL Server 2019 is the latest SQL Server from Microsoft. We need to purchase the license for the editions like Enterprise, Developer, and Standard. SQL Express edition is the free version to use.
What is the Tool available for SQL development?
SQL Server Management Studio(SSMS) is the tool we have to use for SQL development. This tool is available with most of the SQL Server editions. If it's not available we can download and install it manually.
How to connect to SQL Server instance with SQL Server Management Studio(SSMS)?
Step1:
In the windows Run prompt, just type 'ssms' and click ok.
Step 2:
Once the management studio opened, need to enter the below details in the 'Connect to Server' window
a. Server Type:
Four options are available here.
1. Database Engine
2. Analysis Services
3.Reporting Services
4.Integration Services
Select the Database Engine here.
b. Server Name:
Enter the SQL Server Instance, either local server or remote server
c. Authentication:
Below are the options available here.
a.Windows Authentication
b.SQL Server Authentication
c.Active Directory - Universal with MFA Support
d.Active Directory - Password
e.Active Directory - Integrated
Choose the right authentication mode you set at the time of the SQL Server installation.
If you want to connect to any particular database in the server, just click on the options button then select the Connection Properties tab. Now enter the database name you want to connect to in the 'Connect to Database' dropdown.
Finally, click on the Connect button.
After successful authentication, we can see the connected SQL server in the object explorer.
What is the default folder structure of the SQL server?
Once the SQL server connected, we can see the below folder structure in the object explorer.
1. Databases
This folder contains the list of databases on the server. We can create new databases here as well.
2. Security
This folder contains the security features to enable on the server.
a. Logins
b. Server Roles
c. Credentials
etc..
3. Server Objects
This folder contains the other server objects connection details like:
a.Backup Devices
b.Endpoints
c.Linked Servers
c.Triggers
4. Replication
5. PolyBase
6. Always on High Availability
7. Management
8. Integration Service Catalogs
9. SQL Server Agent
It's a windows service provided by Microsoft. Contains the below folders:
a. Jobs
Used to create jobs and schedule the jobs.
b. Alerts
c. Operators
d. Proxies
Used to set permissions to run the Job steps.
e. Error Logs
What is the internal folder structure of the Databases in the SQL Server?
The Databases folder in SQL server contains the below folder structure:
a. Database Diagrams
b. Tables
A place to create and maintain database tables.
c. Views
A place to create and maintain database views or virtual tables.
d. External Resources
e. Synonyms
d. Programmability
A place to create Stored Procedures, Functions, Rules, and Database Triggers.
f. Service Broker
A place to create notifications, queues, and routing. We can enable Service broker service on the database and we can disable as well.
g. Storage
h. Security
This is the place where we can set the database-level security.
What are the basic data types available in SQL server?
Basic Data Types:
1. int
2. bigint
3. smallint
4. tinyint
5. bit
6. varchar
7. varchar(MAX)
8. nvarchar
9. nvarchar(MAX)
10. money
11. decimal
12. float
13. image
14. timestamp
15. date
16. datetime
17. smalldatetime
18. nchar
19. char
20. xml
What are the basic Commands in SQL Server?
SQL Commands:
1. CREATE DATABASE
Command used to create the database in SQL Server.
syntax:
CREATE DATABASE database_name
2. DROP DATABASE
Command used to remove the database from the SQL Server.
syntax:
DROP DATABASE database_name
3. CREATE TABLE
Command used to create a table in the database.
syntax:
CREATE TABLE table_name
(
col1 data_type1,
col2 data_type2,
--
--
col N data_typeN
)
3. DROP TABLE
Command used to remove/delete a table from the database.
syntax:
DROP TABLE table_name
4. ALTER TABLE
Command used to alter/modify the table. Using this we can add new columns or modify the columns and data types on the existing table.
syntax:
ALTER TABLE table_name
add column data_type;
5. SELECT
Command used to retrieve the records/rows from the SQL Table.
syntax:
SELECT (*) or (col 1,col 2..col N) FROM table_name WHERE condition;
Note: * used to retrieve all columns from the table. If you want to get a list of columns or selected columns from the table to specify the columns with comma-separated.
6. UPDATE
Command used to update the table records data.
syntax:
UPDATE table_name
SET column_name
WHERE condition;
7. DELETE
Command used to delete the table data
syntax:
DELETE FROM table_name
WHERE condition;
What is a Table? How to create, update, and delete a table in SQL?
The table is used to represent the relational data in rows and columns.
Once the table created in the SQL, it's stored in Databases -> Tables folder.
Table creation:
syntax:
CREATE TABLE table_name
(
list of column-names with data_types;
)
example:
CREATE TABLE employee
(
employeeid int;
employeename nvarchar(50);
employeesalary money;
)
Output:
| employeeid | empolyeename | employeesalary |
| 101 | A | 10000 |
| 102 | B | 20000 |
Update table:
syntax:
UPDATE table_name
SET column_to_update
WHERE condition
example:
UPDATE employee
SET employeesalary = 15000
WHERE employeeid = 101
Output:
| employeeid | empolyeename | employeesalary |
| 101 | A | 15000 |
| 102 | B | 20000 |
Delete Table data:
syntax
DELETE FROM table_name
WHERE condition
example:
DELETE FROM employee
WHERE employeeid = 101
Output:
| employeeid | empolyeename | employeesalary |
| 102 | B | 20000 |
What is SELECT DISTINCT Statement?
Command used to retrieve the only distinct/unique data from the database table. This command eliminates duplicates data.
syntax:
SELECT DISTINCT col1,col2...colN
FROM table_name;
What is a View? How to create a view in SQL server?
A View is a virtual table created on top of an existing table. It contains the selected result_set from the actual table. We can perform the update and delete operations on views as well and the result updated in the actual table and view both.
syntax:
CREATE VIEW view_name
AS
SELECT col1,col2...colN from table_name
WHERE condition;
example:
Actual table: employee
Now create a view on employee table
CREATE VIEW empview AS
SELECT employeeid ,empolyeename ,employeesalary FROM employee WHERE employeesalary< 50000;
Output:
| employeeid | empolyeename | employeesalary |
| 101 | A | 15000 |
| 102 | B | 20000 |
Now update the record in view
example:
update empview
set employeesalary= 16000
where employeeid = 101
Select * from empview // verify the data in view
Output:
| employeeid | empolyeename | employeesalary |
| 101 | A | 16000 |
| 102 | B | 20000 |
Select * from employee; // verify the data in the actual table
Output:
| employeeid | empolyeename | employeesalary |
| 101 | A | 16000 |
| 102 | B | 20000 |
Now delete the record in view:
example:
delete empview
where employeeid = 101
Select * from empview // verify the data in view
Output:
| employeeid | empolyeename | employeesalary |
| 102 | B | 20000 |
Select * from employee; // verify the data in the actual table
Output:
| employeeid | empolyeename | employeesalary |
| 102 | B | 20000 |
What is a Stored Procedure? How to create a Stored Procedure in SQL server?
Stored Procedure is a group of SQL statements executed with one common name. The advantage is that it's compiled only once and stored in server memory in a compiled form. So when we call the SP next time, no need to execute all the statements inside the SP as its available in a compiled form. It reduces the execution time and server consumption for multiple calls.
syntax:
CREATE PROCEDURE procedure_name
( // parameters )
AS
//declare local variables
BEGIN
// sql statements
END
Stored procedure with no parameters:
example:
create procedure empproc // stored procedure to display the employee table records
as
begin
select * from employee
end
Execute the Stored Procedure:
To execute the stored procedure in SQL, use the command 'EXEC' along with the procedure name.
EXEC empproc;
Stored procedure with parameters:
example:
( @empid int) // parameter
as
declare @localempid int; // local variable
begin
set @localempid = @empid;
select * from empview where employeeid =@localempid
end
create procedure empproc // stored procedure to display the records from the view
Note: We can directly use the parameter in the where condition. In this example, we have covered both the local variable and parameter.
execute the stored procedure:
EXEC empproc 102 // we need to specify the parameter value here
Output:
| employeeid | empolyeename | employeesalary |
| 102 | B | 20000 |
What is User-defined function? How to create in SQL Server?
A user-defined function is similar to a stored procedure, and differs on the below points:
1. Unlike SP, the entire function is executed for every call.
2. functions need to call it from select query only.
3. function must return either a single value or a table.
SCALAR Function
syntax:
CREATE FUNCTION function_name (parameters)
RETURN data_type
AS
BEGIN
//local variables
// statements to get scalar value
RETURN(return_value)
END
example:
create function returnsal (@empid int)
returns int
as
begin
declare @sal int;
select @sal = employeesalary from dbo.Employee where employeeid = @empid
return(@sal);
end
execute the function:
select [dbo].[returnsal] (102)
Output:
| 20000 |
TABLE valued function:
syntax:
CREATE FUNCTION function_name (parameters)
RETURN TABLE
AS
RETURN
(
// statements to get tabled data
);
example:
create function returnemp (@empid int)
returns table
as
return(
select * from dbo.Employee where employeeid = @empid
);
execute the function:
select * from [returnemp] (102)
Output:
| employeeid | empolyeename | employeesalary |
| 102 | B | 20000 |
What are the table constraints in SQL Server?
Constraints in SQL are used to apply rules to the table data.
1. NOT NULL
Used to restrict any null data on the column.
2. UNIQUE
Used to allow only unique data to all columns. Unlike the primary key, it allows the NULL values to columns.
3. PRIMARY KEY
Used to apply uniqueness and not null both combinations to all table rows.
4. FOREIGN KEY
Used to apply uniqueness to rows in another table. A foreign key is a primary key in another table.
5. CHECK
Used to apply the specific condition to meet either at the column or table level.
6. INDEX
Used to apply indexes on the table to speed up the search/retrieval process on the table.
These are two types of indexes we can create in SQL:
a.Clustered Index
Based on the unique cluster index keys, sort and store the data rows in the table.
Once declare a PRIMARY KEY constraint on the table, a unique cluster index on the column automatically created. Only one cluster index is allowed on the table because the data rows are stored in the table in only one order.
b.Non-clustered Index
Non clustered indexes are created automatically when UNIQUE constraint defined on the table. These index keys are having different order than actual table data. As maintains the pointers to each data row, non-clustered indexes are able to locate the data rows quickly on tables.
Conclusion
This blog covered all the basic concepts in SQL Server which helps to get sufficient working knowledge of all concepts.
No comments:
Post a Comment