Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

DBMS Practical ......, Schemes and Mind Maps of Database Management Systems (DBMS)

CT UniversityDatabase Management Systems (DBMS)

DBMS Practical is my own work .........

Typology: Schemes and Mind Maps

2022/2023

Uploaded on 11/03/2023

diwanshu-sharma
diwanshu-sharma 🇮🇳

1 document

1 / 48

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
CT Institute of Engineering and Management
Task 1 : Introduction to SQL and installation of SQL Server :
Introduction To SQL :
•SQL stands for Structured Query Language. It is used for storing and managing data in relational
database management system (RDMS).
•It is a standard language for Relational Database System. It enables a user to create, read, update
and delete relational databases and tables.
•All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard
database language.
•SQL allows users to query the database in a number of ways, using English-like statements.
Rules for SQL:
•Structure query language is not case sensitive. Generally, keywords of SQL are written in
uppercase.
•Statements of SQL are dependent on text lines. We can use a single SQL statement on one or
multiple text line.
•Using the SQL statements, you can perform most of the actions in a database.
•SQL depends on tuple relational calculus and relational algebra.
SQL process:
•When an SQL command is executing for any RDBMS, then the system figure out the best way to
carry out the request and the SQL engine determines that how to interpret the task.
•In the process, various components are included. These components can be optimization Engine,
Query engine, Query dispatcher, classic, etc.
•All the non-SQL queries are handled by the classic query engine, but SQL query engine won't
handle logical files.
DIWANSHU 2102597 1 | P a g e
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29
pf2a
pf2b
pf2c
pf2d
pf2e
pf2f
pf30

Partial preview of the text

Download DBMS Practical ...... and more Schemes and Mind Maps Database Management Systems (DBMS) in PDF only on Docsity!

 Task 1 : Introduction to SQL and installation of SQL Server :

 Introduction To SQL :

• SQL stands for Structured Query Language. It is used for storing and managing data in relational

database management system (RDMS).

• It is a standard language for Relational Database System. It enables a user to create, read, update

and delete relational databases and tables.

• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard

database language.

• SQL allows users to query the database in a number of ways, using English-like statements.

 Rules for SQL :

• Structure query language is not case sensitive. Generally, keywords of SQL are written in

uppercase.

• Statements of SQL are dependent on text lines. We can use a single SQL statement on one or

multiple text line.

• Using the SQL statements, you can perform most of the actions in a database.

• SQL depends on tuple relational calculus and relational algebra.

 SQL process:

• When an SQL command is executing for any RDBMS, then the system figure out the best way to

carry out the request and the SQL engine determines that how to interpret the task.

• In the process, various components are included. These components can be optimization Engine,

Query engine, Query dispatcher, classic, etc.

• All the non-SQL queries are handled by the classic query engine, but SQL query engine won't

handle logical files.

 Installation of SQL Server :

  1. Visit the Microsoft Website and Download SQL Server Express.

• Express edition

• To start download ,click Download Now.

  1. Once downloaded the installation wizard has 3 options:

• Basic - Install SQL Server with the default options

• Custom - This option allows you to select the components to install.

• Download Media - This option allows you to download the installer and run the install later.

  1. Downloading and Installation of Setup file
  2. After installation , we see the following wizard in which we click on “Connect Now” to connect with SQL Server.
  3. After clicking “Connect Now “, we automatically proceeds to Command prompt which assures that the installation is done successfully and
  1. In step7 fig. we see that it is allowing us to click on “Install SSMS” button, and download SSMS (to test connection to SQL Server Express).
  2. Click “Install” to start SSMS with azure Data studio setup.
  3. Installing Overall & Packages progress of SSMS with Azure Data Studio.

 Task 2 : Data Types, Creating Tables, Insert data in table, Retrieval of Rows

using Select Statement, Conditional Retrieval of Rows, Alter and Drop

Statements.

Different kinds of Data Types: In SQL (Structured Query Language), data types are used to define the type of data that can be stored in a column of a table. Here are some common data types in SQL:

1. Integer Types :

  • INT or INTEGER : Represents whole numbers, both positive and negative.
  • SMALLINT : Represents small whole numbers, often with a smaller range than INTEGER.
  • BIGINT : Represents large whole numbers, typically with a larger range than INTEGER.
  1. Decimal and Numeric Types :
  • DECIMAL(p, s) or NUMERIC(p, s) : Represents exact numeric values with a specified precision (p) and scale (s).
  • FLOAT(p) : Represents approximate numeric values with a specified precision (p).
  1. Character String Types :
  • CHAR(n) : Represents a fixed-length string with a maximum length of n characters.
  • VARCHAR(n) : Represents a variable-length string with a maximum length of n characters.
  • TEXT : Represents variable-length text data.

4 .Date and Time Types:

  • DATE : Represents a date without a time component (e.g., '2023-10-26').
  • TIME : Represents a time without a date component (e.g., '14:30:00').
  • DATETIME : Represents a date and time together (e.g., '2023-10-26 14:30:00').
  • TIMESTAMP : Similar to DATETIME, but with fractional seconds support.

5. Boolean Type :

 BOOLEAN : Represents a true or false value.

  1. Binary Large Object Types :
  • BLOB : Represents binary large objects, such as images or other binary data.
  • CLOB: Represents character large objects, typically used for large text data.
    1. Other Specialized Types :
  • JSON : Stores JSON (JavaScript Object Notation) data.
  • XML : Stores XML (eXtensible Markup Language) data.
    1. NULL Type :Represents a special value denoting the absence of data.

9. User-Defined Types (UDTs) :Some SQL databases allow users to define their own custom data

types.

  1. Interval Types : Represents a duration of time.

11. Row Types :Represents a composite data type that can hold multiple fields.

  • Creating Table In SQL Server : The CREATE TABLE statement is used to create a new table in a database. SYNTAX: - CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, .... );
  • Inserting Data In Table  The INSERT INTO statement is used to insert new records in a table. SYNTAX : - INSERT INTO table_name VALUES (value1, value2, value3, ...); OR - INSERT INTO table_name( column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

ALTER Statements :  The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.  The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

  • ALTER TABLE - ADD Column  To add a column in a table, use the following  Syntax : ALTER TABLE table_name DROP COLUMN column_name;
  • ALTER TABLE - DROP COLUMN : To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):  SYNTAX : ALTER TABLE table_name DROP COLUMN column_name;

- ALTER TABLE - RENAME COLUMN :

• To rename a column in a table, use the following

Syntax : ALTER TABLE table_name RENAME COLUMN old_name to new_name;

 DROP Statement :

The DROP TABLE statement is used to drop an existing table in a database. Syntax : DROP TABLE table_name;

 Working with Null Values :

• A field with a NULL value is a field with no value.

• If a field in a table is optional, it is possible to insert a new record or update a record without

adding a value to this field. Then, the field will be saved with a NULL value.

• It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

• We will have to use the IS NULL and IS NOT NULL operators instead.

1. IS NULL SYNTAX :

SELECT column_names FROM table_name WHERE column_name IS NULL;

2. IS NOT NULL Syntax:

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

 Matching a Pattern from a Table :

• LIKE operator is used to perform the pattern matching task.

• A WHERE clause is generally preceded by a LIKE clause to search for a specified pattern in a

column.

• There are two wildcards often used in conjunction with the LIKE operator:

  1. The percent sign % represents zero, one, or multiple characters.
  2. The underscore sign _ represents one, single character  SYNTAX : SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

A. The_Wildcard :

• The _ wildcard represents a single character.

• It can be any character or number, but each _ represents one, and

only one, character.

• SYNTAX : SELECT column1, column2, ...

FROM table_name WHERE columnN LIKE pattern(_);

B. The % Wildcard :

• The % wildcard represents any number of characters, even zero characters.

• SYNTAX : SELECT column1, column2, ...

FROM table_name WHERE columnN LIKE pattern(%);

2. AVG() 5.MAX()

3. SUM()

1. COUNT():

  • The COUNT() function returns the number of rows that matches a specified criterion.
  • SYNTAX : SELECT COUNT (column_name) FROM table_name WHERE condition;

2. AVG():

  • The AVG() function returns the average value of a numeric column.
  • SYNTAX : SELECT AVG (column_name) FROM table_name WHERE condition;

3. SUM():

  • The SUM() function returns the total sum of a numeric column.
  • SYNTAX : SELECT SUM (column_name) FROM table_name WHERE condition ;

4. MIN():

  • The MIN() function returns the smallest value of the selected column.
  • SYNTAX : SELECT MIN (column_name) FROM table_name WHERE condition ;

 UPDATE Statement:

• The UPDATE statement is used to modify the existing records in a table.

• SYNTAX : UPDATE table_name

SET column1 = value1, column2 = value2, ... WHERE condition;

 DELETE Statement:

• The DELETE statement is used to delete existing records in a table.

• SYNTAX : DELETE FROM table_name

WHERE condition;

 Task 4 : Set Operators, Nested Queries, Joins,Sequences.

 SET Operators:

• SET operators are special type of operators which are used to combine the result of two

queries.

• Operators covered under SET operators are:

1. UNION