On October 3rd we started learning database concepts . first we went on to learn many basic concepts. Here we learnt about the many statements.
INTRODUCTION TO MS SQL SERVER
create database,
collection of tables
create table
collection of rows and columns
constraint
set a rules
Types- Primary, Unique, default, check, Not Null, Foreign Key
DML(DATA MANIPULATION LANGUAGE):
INSERT, UPDATE, DELETE
DQL(DATA QUERY LANGUAGE):
SELECT
WHERE -This is used to refer the destination or the condition for the selected sql statements.
Relational or logical opertaions:
This is used to compare the certain quantities to obtain the valid result.
Then we studied and worked n the topic LIKE and DISTINCT. The LIKE is used to check whether the table contains.
Synonym- It is used when we are giving similar names. Table Alias/Alternative name for your table, view, procedure, function, Trigger etc…
VIEWS — Virtual Tables — depend on a table — System Views(SQL Server),User-Defined views(Custom)
TYPES of VIEWS:
User-Defined views(Custom)
SIMPLE — SINGLE TABLE
COMPLEX — MORE THAN ONE TABLE
JOINS — JOIN CLAUSE
combine rows from 2 or more tables based on a common column.
TYPES OF JOINS:
INNER,
OUTER(LEFT,RIGHT,FULL),
SELF,
CROSS
INDEX :
Faster retrieval of data/improve performance.
Clustered(ONLY ONE) and Non-clustered index — Avoid creating too many index on a table — create an index on only frequently used columns.
CLUSTERED — PRIMARY KEY -> AUTOMATICALLY CLUSTERED INDEX IS CREATED — LEAF NODE -> STRUCTURED FORMAT — CLUSTERED TABLE.
NON-CLUSTERED — UNIQUE KEY -> AUTOMATICALLY UNIQUE NON-CLUSTERED INDEX IS CREATED — UNSTRUCTURED FORMAT.
GROUPING SETS:
EXTENSION TO YOUR GROUP BY.
ROLLUP :
EXTENSION OF GROUP BY CLAUSE.
CUBE:
EXTENSION OF GROUP BY CLAUSE.
The DELETE statement conflicted with the REFERENCE constraint “FK__Orders__OrderID__44FF419A”. — The conflict occurred in database “TE”, table “dbo.Orders”, column ‘OrderID’.
The UPDATE statement conflicted with the REFERENCE constraint “FK__Orders__CID__45F365D3”. The conflict occurred in database “TE”, table “dbo.Orders”, column ‘CID’.
alter the constraint/rule for the column CID->Orders table.
RAISERROR:
3 PARAMETERS →
1. MESSAGE
2. SEVERITY — 0 TO 24–0–9 -> Not severe -> error status information — 11–16 -> Runtime errors — error messages are created by the user — 17–19 -> errors that cannot be rectified by the developer and redirected to the system administrator — 20–24 -> Fatal errors — Most severe —
3. STATE — 0–127 — TRY/CATCH WITH RAISERROR
SUBQUERY:
QUERY WITHIN A QUERY — NESTED QUERY — SELECT,INSERT,UPDATE,DELETE — WHERE,FROM — OUTER QUERY(MAIN QUERY) — INNER QUERY(NESTED QUERY) — 32 LEVELS
TYPES OF SUB QUERIES
1. INDEPENDENT NESTED QUERY
2. DEPENDENT NESTED QUERY(CORRELATED QUERY) — INDEPENDENT SUB QUERIES — IN,NOT IN, ANY, ALL.
DIFFERENCES BETWEEN FUNCTION AND STORED PROCEDURE — STORED PROCEDURE
1. Perform a task in an orderly manner
2. Procedures cannot be used within a query
3. DML can be performed
4. Call a function in a procedure
FUNCTION
1. Perform Calculations/operations — with or without parameters
2. Functions can be used within a query (select)
3. No DML
4. Cannot call a procedure in a function
STORED PROCEDURE
Sub-routine/sub-program
Combine all the set of queries which are frequently accessed into a Stored Procedure(Select, Insert, Delete, Update) — Save time — Improve the performance — Stored in the form of pre-compiled code — Create a Stored Procedures — input/output parameters — TYPES — SYSTEM & USER-DEFINED
TEMPORARY PROCEDURES
created and reflected in the tempdb(under Sysytem Databases)
GLOBAL AND LOCAL
All database objects that are created for temporary purpose are prefixed with: — Local — # — Global — ##
ADVANTAGES
1. Reduce Network Traffic
2. Improved Performance
3. Reusable
4. Secure the information
TRANSACTION
ACCOUNTS — A(DEBIT) AND B(CREDIT)
ACID PROPERTY
1.ATOMICITY — SUCCESS-COMMIT/FAILURE-ROLLBACK
2.CONSISTENCY — SUCCESS-COMMIT/FAILURE-ROLLBACK <- STATE -> DATABASE
3.ISOLATION — MULTIPLE TRANSACTIONS -EACH TRANSACTION MUST BE INDEPENDENT
4.DURABILITY — PERMANENT CHANGES IN THE DATABASE
TYPES OF TRANSACTION
1. AUTO-COMMIT — DEFAULT TRANSACTION
2. EXPLICIT TRANSACTION — COMMIT,ROLLBACK,SAVEPOINT
3. IMPLICIT TRANSACTION
TRIGGERS
DDL TRIGGER — CREATE,ALTER,DROP
DML TRIGGER — INSERT,UPDATE,DELETE
CLR TRIGGER — COMMON LANGUAGE RUNTIME
LOGON TRIGGER
We learnt the basics of Database for 1 week (6 days). Various task has been assigned for us and we also worked on those tasks. and had the practical knowledge.
TASK19:
Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table
using the alias name as <WORKER_NAME>.
Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in
upper case.
Q-3. Write an SQL query to fetch unique values of DEPARTMENT from
Worker table.
Q-4. Write an SQL query to print the first three characters of
FIRST_NAME from Worker table.
Q-5. Write an SQL query that fetches the unique values of
DEPARTMENT from Worker table and prints its length.
SQL TASK 1
Q-6. Write an SQL query to print the FIRST_NAME and LAST_NAME
from Worker table into a single column COMPLETE_NAME. A space
char should separate them.
Q-7. Write an SQL query to print details for Workers with the first name
as “Vipul” and “Satish” from Worker table.
Q-8. Write an SQL query to print details of Workers with DEPARTMENT
name as “Admin”.
Q-9. Write an SQL query to print details of the Workers whose
FIRST_NAME ends with ‘a’.
Q-10. Write an SQL query to print details of the Workers whose
FIRST_NAME contains ‘a’.
TASK20:
-> Show the working of the following concepts:
1. Synonyms
2. Index
3. Simple and Complex Views
4. Sequence
-> EXPLORE THE FOLLOWING:
a. String functions
b. Math functions
c. Datetime functions
TASK21:
1. From the following table, create a view for those salespeople who
belong to the city of New York.
2. From the following table, create a view for all salespersons. Return
salesperson ID, name, and city.
3. From the following table, create a view to count the number of
salespeople in each city. Return city, number of salespersons.
Sample table: salesman
4. From the following table, create a view that counts the number of
customers in each grade.
Sample table: customer
5. From the following tables, create a view to get the salesperson and
customer by name. Return order name, purchase amount,
salesperson ID, name, customer name.
TASK22:
Write a query for the following concepts:
1. Scalar Function with 2 parameters — any concept
2. Procedure with OUTPUT parameter
3. Trigger to restrict DML access between 6:00PM to 10.00AM
4. Server-scope trigger to restrict DDL access
5. Working of explicit transaction with Save transaction
6. Difference between throw and Raiserror in exception handling