This tutorial helps you in learning Structured
Query Language and also to work on SQL commands which provides instant result.
SQL
SQL is a language for database. SQL stands for Structured Query Language;
it is used for storing, retrieving and manipulating data in relational
databases.
The various types of SQL
are;
- T-SQL for MS SQL Server.
- JET
SQL (native format) for MS Access.
- PL/SQL for Oracle and
- ANSI
SQL for all databases. American
National Standards Institute (ANSI) published a universal standard for the
SQL which database vendors could adhere.
SQL Language/Commands
SQL Language/commands are used to perform various functions.
These functions include building database objects, manipulating objects, loading
data to database tables, updating existing data in tables, deleting data,
performing database queries, controlling database access, and overall database
administration.
The
various types of SQL Language/Commands are;
·
DDL (Data Definition Language) – Allows users to
create, modify and deletion of database objects (Database, Tables and columns,
Index, View, Synonym, Procedure, Function and Trigger)
The various DDL commands are;
o
CREATE TABLE
o
ALTER TABLE
o
DROP TABLE
o
ALTER COLUMN
o
DROP COLUMN
o
CREATE INDEX
o
ALTER INDEX
o
DROP INDEX
o
CREATE VIEW
o
DROP VIEW
o
CREATE SYNONYM
o
DROP SYNONYM
o
CREATE PROCEDURE
o
DROP PROCEDURE
o
CREATE FUNCTION
o
DROP FUNCTION
o
CREATE TRIGGER
o
DROP TRIGGER
o TRUNCATE TABLE - is a DDL due to the following functionalities compared to DELETE.
deallocates the data pages in a table and only this deallocation is stored in transaction log
acquires only table and page locks for the whole table. since no row locks are used less memory is required (lock is a pure memory object)
resets identity column if there is one
removes ALL pages. NO empty pages are left behind in a table
doesn't fire delete triggers
·
DML (Data Manipulation Language) – Allows users to
manipulate data within objects of a relational database.
The various DML commands are;
o INSERT
o UPDATE
o DELETE
·
DQL (Data Query Language) – Allows users to select/fetch
data from database.
o
SELECT
·
DCL (Data Control Language) – Allows users to control
access to data within the database.
The various DCL commands are;
·
Data administration commands - Database administration is the overall administration of a database.
Allows users to perform audits and perform analyses on operations/system
performance within the database
o
ALTER SERVER
AUDIT
·
Transactional Control Language (TCL)– Allows users to manage the database
transactions.
The various TCL commands are:
o COMMIT - Saves all transactions to database of
current session.
o ROLLBACK - Undoes all the transactions from
database of current session.
o SAVEPOINT - Creates points within groups of
transactions in which to ROLLBACK.
o SET TRANSACTION - Places a name on a transaction.
SQL Server Architecture
SQL Server Architecture is built with the following major
components.
1.
The Network Interface Layer
2.
Relational Engine
3.
Storage Engine and
4.
SQL OS
1. The Network
Interface Layer
SQL server has a layer that enables its communication with the
outer environment. This layer is the SQL Server Network Interface (SNI) layer.
It comprises of three important components:
a) The Network Protocols
b) TDS (Tabular Data Stream)
c) The Protocol layer
a) The Network Protocols: There are four protocols supported by SQL
Server.
· Shared Memory: This is the default protocol used to connect to a client residing locally (i.e. on the same machine).
· TCP/IP: This protocol helps us to connect to the SQL server over the network via an IP address and a port number. The default TCP port used by SQL Server is 1433.
· Named Pipes: Originally developed for LAN, this protocol is similar to TCP/IP, but is efficient for slower networks like WAN. It uses 445 as port number.
· VIA: Virtual Interface Adapter is a protocol used for high performance communication between two connected systems, with the help of specialized hardware on both ends.
b) Tabular Data Stream
(TDS): A
protocol used for interacting with database servers. The moment a TCP/IP
connection is established, a corresponding link to the TDS endpoint is made for
the client-server communication.
c) The Protocol Layer: This layer unwraps the TDS packet that was
created at the client’s side. Also, the results sent to the client by the
server, are packaged by this layer.
2. Relational
Engine
Relational Engine also called as the query processor, It includes
the components of SQL Server that determine what your query exactly needs to do
and how best way to do it. It manages the execution of queries as it requests
data from the storage engine and processes the results returned to Protocol
layer.
The various tasks of Relational Engine are:
·
Query Processing
·
Memory Management
·
Thread and Task Management
·
Buffer Management
·
Distributed Query Processing
3. Storage Engine
Storage Engine is responsible for storage and retrieval of
the data on to the physical storage system (Hard Disk, SAN etc.). Let’s understand
more in details as given below.
Any database in SQL server, there are two types of files (Data
file and Log file) that are created at the disk level.
Data file - Data file physically stores the data in data pages.
Log file - Log
file used for write logs which are used for storing transactions performed on
the database.
See the following more details on data file and log file.
Data File: Data
File stores data in the form of Data
Page (8KB) and these data pages are logically organized in extents.
Extents: Extents are
logical units in the database. They are a combination of 8 data pages i.e. 64
KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed
extents hold different types of pages like index, system, data etc (multiple
objects). On the other hand, Uniform extents are dedicated to only one type
(object).
Pages: As we should
know what type of data pages can be stored in SQL Server, below mentioned are
some of them:
· Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
· Index: It stores the
index entries.
· Text/Image: It
stores LOB ( Large Object Data) like text, ntext, varchar(max),
nvarchar(max), varbinary(max), image and xml data.
· GAM & SGAM (Global Allocation Map & Shared Global
Allocation Map): They are used for saving
information related to the allocation of extents.
· PFS (Page Free Space):
Information related to page allocation and unused space available on pages.
· IAM (Index Allocation Map): Information pertaining to extents that are used by a table
or index per allocation unit.
· BCM (Bulk Changed Map):
Keeps information about the extents changed in a Bulk Operation.
· DCM (Differential Change Map): This is the information of extents that have modified since
the last BACKUP DATABASE statement as per allocation unit.
Log File: It also
known as write ahead log. It stores modification to the database (DML and DDL).
The sufficient information is logged to be able to:
·
Roll back transactions if requested.
·
Recover the database in case of
failure.
·
Write Ahead Logging is used to
create log entries.
o
Transaction logs are written in
chronological order in a circular way.
o
Truncation policy for logs is based
on the recovery model.
4. SQL OS
This lies between the
host machine (Windows OS) and SQL Server. All the activities performed on
database engine are taken care of by SQL OS. It is a highly configurable
operating system with powerful API (Application Programming Interface),
enabling automatic locality and advanced parallelism. SQL OS provides various
operating system services, such as memory management deals with buffer pool,
log buffer and deadlock detection using the blocking and locking structure.
Other services include exception handling, hosting for external components like
Common Language Run-time, CLR etc. For more details refer the following link.
SQL Processing Architecture
SQL Processing Architecture represents how Microsoft SQL Server queries are processed on the client, how the various clients interact with SQL Server, and what SQL Server does to process clients' requests. The high-level diagram is given below. Let’s understand more details on this topic, refer the following link.
Query Execution
Evaluation Order
The Optimizer will try to find the most efficient
way/plan to execute the query based on available information such as table Index
and scanning mechanism.The Query execution default order is:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
Example:
SELECT d.DepartmentID, d.DepartmentName,
COUNT(e.EmployeeID) AS
NumberOfEmployees FROM Employee e
INNER JOIN Department d ON d.DepartmentID = e.DepartmentID
WHERE Gender ='M'
GROUP BY d.DepartmentID, d.DepartmentName
HAVING COUNT(EmployeeID)>=2
ORDER BY DepartmentName
Nice Tutorials.
ReplyDeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai Project Centers in Chennai
Nice article thanks for given this information. I hope it useful to many People
ReplyDeleteSQL Azure Online Training
Azure SQL Training
SQL Azure Training
As we know there are many companies which are converting into Big data app development. with the right direction we can definitely predict the future.
ReplyDeleteIt is very helpful and very informative and I really learned a lot from it.
ReplyDeleteAzure DevOps Online Training
Microsoft Azure DevOps online Training in Hyderabad
Really it is very useful for us..... the information that you have shared is really useful for everyone.Excellent information. Very useful to everyone and thanks for sharing this.
ReplyDeletePHP Training in Chennai | Certification | Online Training Course | Machine Learning Training in Chennai | Certification | Online Training Course | iOT Training in Chennai | Certification | Online Training Course | Blockchain Training in Chennai | Certification | Online Training Course | Open Stack Training in Chennai |
Certification | Online Training Course
nice information thank u so much oracle training in chennai
ReplyDeleteNice post.
ReplyDeletesplunk admin online training
splunk admin training
splunk development online training
splunk development training
splunk online training
splunk training
sql azure online training
sql azure training
sql plsql online training
sql plsql training
sql server dba online training
sql server dba training
sql server developer online training
sql server developer training
https://programmingtutorialsscript.blogspot.com/
ReplyDeleteReally nice blog, very infromative. You can refer more topics related to SQL like SQL Commands and Advanced SQL from here for future articles
ReplyDeletethanks !
https://programmingtutorialsscript.blogspot.com/
ReplyDeletehttps://programmingtutorialsscript.blogspot.com/
ReplyDeleteThanks for sharing a very useful article.
ReplyDeleteDevOps Training
DevOps Online Training
Really nice blog, very infromative. You can refer more topics related to SQL like SQL Commands and PLSQL, PLSQL cursors from here for future articles
ReplyDeletethanks !
Its a great post focusing on SQL and other aspects to find out how far can one go and utilise all these.
ReplyDeleteSQL Server Load Soap API
C Programming Tutorial for Beginner with Free Certification
ReplyDelete"Our main objective is to teach everyone value
ReplyDeleteInvesting so that one can become financial independent "
Visit
"Our main objective is to teach everyone value
ReplyDeleteInvesting so that one can become financial independent "
Visit
Thank you for sharing this informative post. Looking forward to reading more.
ReplyDeleteBest PHP Development Services
Thank you for sharing programming tutorials (https://www.programingtutorial.com/)
ReplyDeleteabinitio online training
ReplyDeletespark online training
scala online training
azure devops online training
Nice Post Oracle Cloud Automated Testing Tool
ReplyDeletewill omit your great writing due to this problem.
ReplyDeletedata science training
python training
angular js training
selenium trainings
Nice Post Oracle Cloud Automated Testing Tool
ReplyDeletehttps://www.programingtutorial.com/
ReplyDeletenice post.splunk training
ReplyDeletesplunk online training
Nice Post Oracle Cloud Automated Testing Tool
ReplyDeleteGreat Post on SQL Certification
ReplyDeletenice post.sql server developer training
ReplyDeletesql server developer online training
Nice Post Oracle Cloud Automated Testing Tool
ReplyDeletenice post.aws training
ReplyDeleteaws online training
Helpful blog
ReplyDeletebest digital marketing agency
sql server dba online training
ReplyDeleteoracle golden gate online training
nice post.dot net training
ReplyDeletedot net online training
dot net online course
ReplyDeleteDevOps is a new trend in software development over the past few years. Although the term is relatively new, it is actually a combination of many practices that have been in use for years. DevOps allows software to be released quickly and efficiently, while still maintaining high levels of security. Azure devops backup
abinitio online training
ReplyDeletespark online training
scala online training
azure devops online training
tableau online training
SAP BW on Hana online training
Thank you for such an informative blog and there are so many websites where you can learn SQL commands. MySQL developers can use the visual Query builder to drag and drop database tables, build SQL queries, build SQL Server database ad hoc reports, parameterized reports, online data dashboards, build D3 based charts, and schedule reports-all with point and click simplicity and database security. You can go for SQL tutorial on any website. Datasparc also provide online tool to run commands.
ReplyDelete
ReplyDeleteThanks For the Great Content. I Will also share with my Friends & once again thanks a lot.
Buy Office 2019 Professional Plus Product Key
Buy Office 2019 Professional Plus Product Key in very cheap price.
Thanks For the Great Content. I Will also share with my Friends & once again thanks a lot.
ReplyDelete.Net Online Training Hyderabad
Visit us: dot net training
Train yourself in specific software modules to brush up your skills & shine in your career growth with the best software training institute in Chennai, Infycle Technologies. Infycle offers the Best Data Science Training in Chennai to serve the candidate's job profile requirements, including the top job placements in the MNC's. Rather than just teaching the theories, our fundamental aim is to make you a master by giving you live hands-on training. Therefore, individuals will be asked to work on the live tasks & real-time use cases that bring out the definite coder in you! To grab all these, call 7502633633 for a free demo.
ReplyDeleteThanks for this post..SQL Server course has been created by Colt Steele who is an experienced and successful bootcamp instructor. He has condensed his SQL bootcamp curriculum into this online course very well. The course imparts a solid foundation in databases using MySQL, the most popular open source SQL database out there.
ReplyDeleteHTML and CSS Online Course
Programming Languages Course
Very useful information.Thankyou so much for this wonderful blog…Great work keep going. Looking for the best database services in Hyderabad hire Cyanous software solutions now.
ReplyDeleteBest Database services in Hyderabad
Best software & web development company in Hyderabad
nice post.splunk training
ReplyDeletesplunk online training
splunk online course
Thanks For Sharing This Useful Blog.
ReplyDeleteVisit us: Java Online Training Hyderabad
Visit us: Java Training
nice post.tableau online training
ReplyDeleteThanks for this post
ReplyDeleteBest Python Online Course
Best Python Online Course Hyderabad
It's really Good Blog!! Thank you for sharing with us..
ReplyDeleteDevOps Training
DevOps Online Training
Nice information. Thanks for sharing such an amazing article. For online training and Self Paced courses visit our site click4learning.com/
ReplyDeleteI am quite impressed with the content of this article.
ReplyDeleteVisit us: Dot Net Online Course
Visit us: .Net Online Training
nice post.informatica online training
ReplyDeleteExcellent Blog, I like your blog and It is very informative. Thank you.
ReplyDeleteVisit us: RPA Ui Path Online Training
Visit us: Ui Path Course Online
A decent blog consistently concocts new and energizing data and keeping in mind that perusing I have feel that this blog is truly have each one of those quality that qualify a blog to be a one. sql training
ReplyDeletenice post.aws online training
ReplyDeleteNice Blog, Thank for sharing .
ReplyDeleteSQL training in vizag
Nice Post Oracle Cloud Automated Testing Tool
ReplyDeleteGood Post. I like your blog. Thanks for Sharing.
ReplyDeleteVisit us: Core Java Online Course
Visit us: Best Online Java Course
Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.Mobilemall Bangladesh
ReplyDeleteNice post, Thanks for sharing!
ReplyDeleteDevOps Training
DevOps Online Training
Infycle Technologies, the best software training institute cum placement center in Chennai offers the best Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to the Digital Marketing Training, other in-demand courses such as AWS, DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.Best Digital Marketing Course in Chennai | Infycle Technologies
ReplyDeleteGrab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for having a free demo.
ReplyDeleteThe overview blog on SQL is really so interesting and how it opens an opportunity window for people who are specifically targetting this field. if you want to understand such topic further you can check out data science course in bangalore
ReplyDeleteHi, This is so informative blog, I am doing course on SQL Online and this blog is really informative for me. Thank you for this blog!
ReplyDeleteYour blog has wonderful information regarding Cloud Services, I also have some valuable information regarding the Best Cloud Application Development Services in USA
ReplyDeleteتنظيف مجالس بالدمام
ReplyDeleteشركة تنظيف سجاد بالدمام
شركة تسليك مجاري بالدمام
شركة تنظيف شقق بالدمام
Infycle Technologies, the top software training institute and placement center in Chennai offers the Best Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to Digital Marketing, other in-demand courses such as DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.
ReplyDeleteInfycle Technologies, the No.1 software training institute in Chennai offers the No.1 Big Data Hadoop Training in Chennai | Infycle Technologies for students, freshers, and tech professionals. Infycle also offers other professional courses such as DevOps, Artificial Intelligence, Cyber Security, Python, Oracle, Java, Power BI, Selenium Testing, Digital Marketing, Data Science, etc., which will be trained with 200% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7502633633 to get more info and a free demo.
ReplyDeleteNice work, truly valuable to me.
ReplyDeleteI hope you keep it up.
We are offering best offshore development services then,
Visit here:
Iyrix Technologies
Remote Software Developers
Software Development Services
SEO and Online Marketing Services
Python Course online
ReplyDeletePython online training
I liked this article very much. The content is very good. Keep posting.
ReplyDeleteBest SQL Server Online Training Certification in Hyderabad
Great post. Thanks for sharing. Keep Sharing.
ReplyDeleteDo you want to enroll for an Azure course in Delhi? . Then Ducat is of the finest option chosen by the student where they offer quality education under the guidance of experienced and trained teaching staff.
Hey! Love the way you write, I would like to thank you for sharing this interesting information. I appreciate reading it. This is very useful & informative for me. Thanks for sharing with us.
ReplyDeleteAlso check out
Warehouse Rack
Warehouse Storage Rack
Very informative. Now I got an idea about this topic. thanks for the content.
ReplyDeleteCCNA Training In Chennai
CCNA Training Online
CCNA Course In Coimbatore
cloudkeeda
ReplyDeletewhat is azure
azure free account
cloudkeeda
cloudkeeda
cloudkeeda
cloudkeeda
cloudkeeda
cloudkeeda
I really like and appreciate your post.Really thank you! Fantastic.
ReplyDeletesharepoint training
sharepoint online training
More impressive blog!!! Thanks for shared with us.... waiting for you upcoming data.
ReplyDeleteSoftware Testing Course in Chennai
Software Testing Online Training
Software Testing Training in Coimbatore
Thanks for sharing. more impressive blog. thanks for shared with us.
ReplyDeleteJava developer job available in Chennai
Thanks for this post...Elegant Training of SQL Server Course Dubai will help you gain a full understanding of this universal programming language. You'll start by learning key concepts and move on to more advanced topics as you progress through the lessons. By the end of the series, you'll have a solid working knowledge of SQL.
ReplyDeleteGraphic Designing Course in Dubai
best web Development courses Dubai
Programming Languages Course Dubai
Nice Post. I like your blog. Thanks for Sharing.
ReplyDeleteDevOps Training
DevOps Online Training
We provide support for all major databases, including Oracle MySQL, Percona MySQL, PostgreSQL, MongoDB, and associated platforms. Our experts have adequate knowledge of all databases. You are at the perfect destination to manage your database systems. Genex's foremost objective is clients satisfaction and works hard towards your needs. Data is a very important record and a confidential thing to safeguard.
ReplyDeletehttps://genexdbs.com/
Nice Article.Thanks for sharing this post.
ReplyDeleteDevOps Training
DevOps Online Training
This is the perfect webpage for everyone who hopes to understand this topic. You know a whole lot its almost tough to argue with you (not that I personally would want to…HaHa). You definitely put a brand new spin on a topic that's been written about for ages. Excellent stuff, just wonderful! Webinfotechsolution
ReplyDeletehttps://mysqlknowledgebank.blogspot.com/2020/06/working-with-python-file-handling.html?showComment=1643910037422#c2650056577462506782
ReplyDeleteSpring boot tutorial in Delhi
ReplyDeleteGreat Post. Very informative. Keep Sharing!!
ReplyDeleteApply Now for Azure Training in Noida
For more details about the course fee, duration, classes, certification, and placement call our expert at 70-70-90-50-90
Nice Post. Very informative. Keep Sharing similar Post.
ReplyDeleteAutoCAD Course in Mumbai
AutoCAD Classes in Mumbai
AutoCAD Training in Mumbai
this is Best Private Universities in India for student placement
ReplyDelete
ReplyDeleteYou really share a great post and keep sharing more content like this! Thanks
high asset divorce in austin, texas
high conflict divorce in austin, texas
That is such a great post. Thank you for sharing
ReplyDeletedigital marketing skill institute
online digital marketing courses
I would say, there are three fundamental kinds of SQL Server Certifications.These are for processes, programming arrangements, and different arrangements. I'm intentionally barring related fields, for example, business knowledge or testing and approval to keep this answer clean.
ReplyDeleteThanks for sharing this.really a great post
ReplyDeleteSQL Kya Hai?
Kotlin Kya Hai?
Great Post. Very informative. Keep Sharing!!
ReplyDeleteApply Now for Azure Training Classes in Noida
For more details about the course fee, duration, classes, certification, and placement call our expert at 70-70-90-50-90
For Education,jobs, news, health, Business, Astrology
ReplyDeletehttp://wisdommaterials.com/
https://www.globalcompaniesinfo.com/
http://wisdomallcodes.com
This post is so usefull and informaive keep updating with more information.....
ReplyDeleteSelenium Training in Bangalore
Selenium Course in Bangalore
Hey,
ReplyDeleteI’ve read your full post, the post is great to enhance our knowledge about ReactJS. I’m damn sure you will keep it up to take it on next level in near future.
We’ve my own blogs about ReactJS, just take give some time to visit. Moreover, having a variety developers too. Just visit given links. Thanks
Iyrix Technologies
Remote Software Developers
Software Development Services
UI/UX Designers
Check Developers Rates
Website: ReactJS Stories
7 Reasons why choose ReactJS
10 Reasons To Use ReactJS For Enterprise App Development
Business Benefits of ReactJS Framework for Modern Web and App Development
Top React Static Site Generators for 2022
Its a great post focusing on SQL and other aspects to find out how far can one go and utilise all these.
ReplyDeletesql tainining
Hi, I have just started to SQL Certification Online. and this blog is really informative for me. Keep us posted for this kind of valuable blog. Thank you for this blog!
ReplyDeleteGreat information. Thanks for sharing
ReplyDeleteMSSQL Database in Chennai