Use parentheses for column lists, function calls, subqueries, and multi-value

statements.

Omit parentheses for single-column constraints and simple expressions.

a) CREATE DATABASE <databasename>
CREATE DATABASE ourworld;

b) USE <databasename>

USE ourworld;

c) SHOW TABLES

SHOW TABLES;

d) DROP DATABASE <databasename>

it will remove all tables along with the database
DROP DATABASE ourworld;

1) CREATE TABLE Products (

product_id INT PRIMARY KEY,

price DECIMAL(10,2) CHECK (price > 0)

);
#2) CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

department VARCHAR(20) CHECK (department IN ('HR', 'Sales', 'IT', 'Finance'))

);
#3)CREATE TABLE Students (

student_id INT PRIMARY KEY,

name VARCHAR(50),

age INT CHECK (age BETWEEN 5 AND 18)

);

d) Default
A constraint that ensures that the attribute is assigned a default value if it is not
assigned any value.
CREATE TABLE Customers (

cust_id INT PRIMARY KEY,

country VARCHAR(50) DEFAULT 'USA'

);
# If no country is given, it defaults to 'USA'.

e) Foreign key constraint

Establishes a relationship between two tables.
The FOREIGN KEY constraint ensures that a column in one table references a
Primary Key in another table.
This means that the values assigned to this column must exist in the referenced
table.

CREATE TABLE Departments (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(50) NOT NULL

);
CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50) NOT NULL,

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

f) UNIQUE

• Ensures no duplicate values in a column.

• Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints.

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

email VARCHAR(100) UNIQUE

);
email must be unique, but NULL values are allowed.
CREATE TABLE student

(Adno Numeric (3) Primary Key,

Name varchar (20) not null,
Class Numeric (2),
Section char (1),
Fees numeric (10, 2));

Viewing Table
Syntax Describe Table Name or
DESC tablename
Describe Student

Dropping a Table
Syntax:
DROP TABLE tablename;
➝ Remove the whole structure of student table

Eg: DROP TABLE employee;

INSERT INTo student

VALUES (5, 101, Adhvika', F, '2035-08-241)

INSERT into Student values

(18, 113, 'Aanya', 'F', 12015-12-02)1,

(15,209, 'Dhrceu', 'M, '2015-09-08.),

(08,219, 'Chhavil, 'F1, ' 2015-04-28'),

(34, 200, 'Chhavi', 'F','2015-05-08') ,

(31, 167, 'Akshita, 'F', 2015-31-03'),

(28, 100, 2015-0D- 'Tanush', 'M? -03);

INSERT INTo student

(rollno, enroll-no, name, gender)
VALUES (10, 332, "Anker”,”M”);
For example:
1. Add one new column totalfees with number (10, 2).
ALTER TABLE student
ADD totalfees number(10,2);

2. Change totalfees datatype as number(12,2).

ALTER TABLE student
MODIFY totalfees number(12,2);

3. Remove totalfees column.

ALTER TABLE student
DROP totalfees;

Drop Foreign Key

ALTER TABLE table_name DROP FOREIGN KEY

foreign_key_name;

Assume you have a table named Orders with a foreign key

constraint fk_customer_id referencing the Customers table.
ALTER TABLE Orders DROP FOREIGN KEY fk_customer_id;

Add Foreign key

ALTER TABLE table_name

ADD CONSTRAINT foreign_key_name
FOREIGN KEY (column_name)
REFERENCES parent_table (parent_column);
Adding primary key
ALTER TABLE table name
ADD Constraint (col-name) ;

Eg: ALTER TABLE employee

ADD PRIMARY KEY (empID);

(vii) Dropping a Primary key

ALTER TABLE tablename
Drop constraints
eg Alter table employee
drop primary key;

SELECT Command
to view table information from SQL database.
By using SELECT command, we can get one or more fields information,
while using *, one can get all fields information.

Syntax: SELECT (*/field list)

FROM tablename

[WHERE condition];
We can specify any condition using where clause. Where clause is optional.

Example

1. Display student table information.

SELECT *
FROM student;
This will display all information of the particular table (student) in the database.

2. To display name and class of student table information.

SELECT name, class

FROM student;

3. To display name of 10th class student information.

SELECT name
FROM student
WHERE class = 10;

4)Using DISTINCT keyword:-

→ The DISTINCт keyword is used to remove duplicate records from a table.
Syntax: SELECT DISTINCT col-name
FROM table name;

Arithmetic Operations

Arithmetic operator takes two operands and performs a mathematical

calculation on them.
they can be used only in SELECT command.
The arithmetic operators used in SQL are: + Addition - Subtraction *
Multiplication / Division
Example (string join)

Display first name with second name.

SELECT FirstName + SecondName
FROM Name;
Output:
Select DA*100
From salary;

Relational operators
are used to implement comparison between two operands.
These operators can be used only in 'where clause'.
Relational operators are -
< less than
> greater than
< = less than or equal to
> = greater than or equal to
= equal to
! = not equal to
Logical operators are also possible only in 'where clause' and are used
to merge more than one condition. Logical operators are: AND OR NOT
LIKE OPERATOR The LIKE operator in MySQL is used for pattern
matching in text values. It is commonly used in WHERE clauses to filter
results based on a specified pattern. It works with two wildcard
characters:
1. % (Percent Sign) → Represents zero or more characters.
2. _ (Underscore) → Represents exactly one character.
The IN operator allows us to specify multiple values in a WHERE clause
The IN operator selects values that matches any value given in list of
values.
→ The NOT IN operator finds rows that do not match the list.
Conditions Based on a Range:- →
The BETWEEN operator define a range of values that the column values
must fall -into make the condition true.
→ A The NOT BETWEEN operator is reverse of BETWEEN operator, i.e.,
the rows not satisfying the BETWEEN Condition are retrieved.
(ii) ORDER BY Clause :- → It organises or sorts records based on the values in one or
more cols

Syntax Select expressions

from table
where conditions
order by excpression[Asc|Desc];

Note: By default, records are arranged in ascending order but if we want to specify
the order, we use 'Asc for ascending and 'DESC for descending.
Aggregate functions are used to implement calculation based upon a particular
column.
These functions always return a single value.
Aggregate functions are: 1. SUM() 2. AVG() 3. MAX() 4. MIN() 5. COUNT()
Syntax:
SELECT fn(column name)
FROm table name
GROUP BY is a clause that enables SQL aggregate functions for grouping of
information.
(ie. GROUP BY clause is used in collaboration with the SELECT statement to
arrange identical data into groups.).
This clause is used whenever aggregate functions by group are required
Having Clause

the 'where' clause is used only to place condition on the selected columns,
whereas the 'HAVING' clause is used to place condition on groups created by
'group by' clause, because here the 'WHERE' clause is not useable.

remove where here

Update Command
This command is used to implement modification of the data values.
Syntax:
UPDATE table name
SET colname = newvalue, colname = newvalue
where condition;
Delete Command

This command is used to remove information from a particular row or rows. Please
remember that this command will delete only row information but not the structure
of the table.
To remove all record from table – Delete from tablename
Cartesian Product
SQL joins are used to relate information in different tables
A join condition is a part of the SQL query that retrieves rows from two or more
tables.
If join condition is omitted or if it is invalid, then join operation will result in a
Cartesian product.

Cartesian product is a binary operation and is denoted by (x)

Cartesian product returns a number of rows equal to number of rows in the first
table multiply by number of rows in the second table
At the same time, number of columns equal to number of columns in the first table
added by number of columns in the second table.
Table 1:
Number of rows (cardinality) = 4
Number of columns (degree) = 3
Table 2:
Number of rows (cardinality) = 5
Number of columns (degree) = 4
Cartesian product:
Number of rows (cardinality) = 4*5 = 20
Number of columns (degree) = 3+4 = 7
Equi Join :-
→ The EQUI JOIN is a simple join with a where clause that specifies that the value in
one column of the first table must equal to the value in a corresponding column of
the second table.
→ The join in which columns are compared for equality, is called Equi-Join.
This join can be applied using
the Equal to (=) operator
or the INNER JOIN Key word.
→ It retrieves only common data from two or more tables.

For example
SELECT *
FROM product, customer
WHERE product.product_no = customer. procuct_no;

(or)
SELECT *
FROM product p, customer c
WHERE p.product_no=c.procuct_no;

SELECT
table I.column, table2.colemn,...-
FROM table I INNER JOIN table2
ON tableI.column = table 2-column;
# Natural Join:-
→ The Natural Join is similar to the Equi Join, however the difference is that the
result contains only one column for each pair of identical columne name.
+ There are no columns with the same name in the record set.