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

Structures Query Language, Cheat Sheet of Database Management Systems (DBMS)

University of JammuDatabase Management Systems (DBMS)

This document is about some of the MySql commands.

Typology: Cheat Sheet

2022/2023

Available from 10/02/2024

jasleen-kour
jasleen-kour 🇮🇳

1 document

1 / 35

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1. CREATING A DATABASE
In SQL, the 'Create Database' statement is a first step for storing the
structured data in the database.
SYNTAX
CREATE DATABASE Database_Name;
EXAMPLE
CREATE DATABASE CLASS;
OUTPUT
2. CREATE A TABLE
SQL CREATE TABLE statement is used to create table in a database.
SYNTAX
CREATE TABLE TABLE_NAME (
COLUMN1 DATATYPE,
COLUMN2 DATATYPE,
COLUMN3 DATATYPE,
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

Partial preview of the text

Download Structures Query Language and more Cheat Sheet Database Management Systems (DBMS) in PDF only on Docsity!

1. CREATING A DATABASE

In SQL, the 'Create Database' statement is a first step for storing the structured data in the database.

SYNTAX

CREATE DATABASE Database_Name;

EXAMPLE

CREATE DATABASE CLASS;

OUTPUT

2. CREATE A TABLE

SQL CREATE TABLE statement is used to create table in a database.

SYNTAX

CREATE TABLE TABLE_NAME (

COLUMN1 DATATYPE,

COLUMN2 DATATYPE,

COLUMN3 DATATYPE,

EXAMPLE

CREATE TABLE STUDENT(

ID INT PRIMARY KEY,

NAME VARCHAR (20),

ADDRESS VARCHAR (30),

AGE INT,

PINCODE INT,

MARKS INT,

DEPARTMENT VARCHAR (25)

OUTPUT

SELECT * FROM STUDENT;

OUTPUT

4. SELECT ROW FROM TABLE

The SELECT statement is used to select data from a database.

SYNTAX

SELECT column1, column2, ... FROM table_name;

EXAMPLE

SELECT * FROM STUDENT;

OUTPUT

5. WHERE CLAUSE

The WHERE clause is used to filter records. It is a data manipulation language statement.

SYNTAX

SELECT column1, column2, ... FROM table_name WHERE condition;

EXAMPLE

SELECT NAME FROM STUDENT WHERE DEPARTMENT='MCA';

OUTPUT

6. (a) WHERE CLAUSE USING AND / OR

SYNTAX

SELECT COLUMN1, COLUMN2,…

FROM TABLE_NAME

WHERE

CONDITION1 AND CONDITION2…..;

EXAMPLE

SELECT ID, NAME, ADDRESS FROM STUDENT WHERE ID=103 AND

NAME='SMAYLA';

OUTPUT

WHERE column LIKE pattern;

EXAMPLE

SELECT * FROM STUDENT WHERE NAME LIKE 'A%';

OUTPUT

EXAMPLE

SELECT * FROM STUDENT WHERE NAME LIKE '%A';

OUTPUT

9. ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

SYNTAX

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

EXAMPLE:- USING ASC COMMAND

SELECT * FROM STUDENT ORDER BY ID ASC;

OUTPUT

EXAMPLE:- USING DESC COMMAND

SELECT * FROM STUDENT ORDER BY ID DESC;

OUTPUT

EXAMPLE:

SELECT * FROM STUDENT WHERE NOT AGE BETWEEN 21 AND 23;

OUTPUT

11. IN/ NOT IN OPERATOR

IN OPERATOR

The IN operator allows you to specify multiple values in a WHERE clause.

SYNTAX

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

EXAMPLE

SELECT NAME FROM STUDENT WHERE ADDRESS

IN('JAMMU','UDHAMPUR');

OUTPUT

NOT IN OPERATOR

SYNTAX

SELECT COLUMN1, COLUMN2…

FROM TABLE_NAME

WHERE NOT COLUMN_NAME IN (VALUE1, VALUE2…..);

EXAMPLE

SELECT NAME FROM STUDENT WHERE ADDRESS NOT

IN('JAMMU','UDHAMPUR');

OUTPUT

13. UPDATE

The UPDATE command is used to update existing rows in a table.

SYNTAX

UPDATE TABLE_NAME SET COLUMN1=VALUE1,

COLUMN2=VALUE2…….

WHERE CONDITION;

EXAMPLE:

UPDATE STUDENT SET NAME='ANJALI' WHERE ID=102;

OUTPUT

SELECT * FROM STUDENT;

OUTPUT

14. THE SQL COUNT (), AVG () AND SUM () FUNCTIONS

COUNT ()

The COUNT () function returns the number of rows that matches a specified criterion.

SYNTAX

SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;

EXAMPLE

SELECT COUNT (ID) FROM STUDENT;

OUTPUT

SUM ()

The SUM () function returns the total sum of a numeric column.

SYNTAX

SELECT SUM(COLUMN_NAME) FROM TABLE_NAME;

15. THE SQL MIN () AND MAX () FUNCTIONS

MIN ()

The MIN () function returns the smallest value of the selected column.

SYNTAX

SELECT MIN(COLUMN_NAME) FROM TABLE_NAME;

EXAMPLE

SELECT MIN(ID) FROM STUDENT;

OUTPUT

MAX ()

The MAX () function returns the largest value of the selected column.

SYNTAX

SELECT MAX(COLUMN_NAME) FROM TABLE_NAME;

EXAMPLE

SELECT MAX(ID) FROM STUDENT;

OUTPUT

16. GROUP BY

The GROUP BY statement is often used with aggregate functions (COUNT (), MAX (), MIN (), SUM (), AVG ()) to group the result-set by one or more columns.

SYNTAX

SELECT COLUMN1, SUM(COLUMN_NAME) FROM TABLE_NAME

GROUP BY COLUMN1;

EXAMPLE

SELECT ADDRESS, SUM(ID) FROM STUDENT GROUP BY

ADDRESS;

OUTPUT

18. CONSTRAINTS (PRIMARY KEY, NOT NULL, UNIQUE,

DEFAULT, CHECK&FOREIGN KEY)

NOT NULL

The NOT NULL constraint enforces a column to not accept NULL values, which means that you cannot insert or update a record without adding a value to this field.

SYNTAX FOR NOT NULL:

CREATE TABLE TABLE_NAME(

COL_1 DATA TYPE NOT NULL,

COL_2 DATA TYPE,…,COL_N DATA TYPE);

DEFAULT

The DEFAULT constraint provides a default value for a column.

SYNTAX FOR DEFAULT:

CREATE TABLE TABLE_NAME(

COL_1 DATA TYPE,

COL_2 DATA TYPE DEFAULT: VALUE,….., COL_N DATA TYPE);

UNIQUE

The UNIQUE constraint ensures that all values in a column are unique.

SYNTAX FOR UNIQUE:

CREATE TABLE TABLE_NAME(

COL_1 DATA TYPE UNIQUE,

COL_2 DATA TYPE,…, COL_N DATA TYPE);

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table.A table can have only one primary key, which may consist of one single or of multiple fields.

SYNTAX FOR PRIMARY KEY:

CREATE TABLE TABLE_NAME(

COL_1 DATA TYPE PRIMARY KEY,

COL_2 DATA TYPE,…, COL_N DATA TYPE);

CHECK

The CHECK constraint limits the value that can be placed in a column.

SYNTAX FOR CHECK:

CREATE TABLE TABLE_NAME(

COL_1 DATA TYPE CHECK(CONDITION),

COL_2 DATA TYPE,…, COL_N DATA TYPE);