Keys in database
Keys are important in DBMS; keys help to make each record uniquely in the table. Keys are
also important for making relations between tables and for data integrity. There are basically
four different types of keys:
Primary key: A primary key or simply a key is a field that uniquely identifies a row in a
table.
Candidate key: All the field values that are eligible to be the primary key are the
candidate keys for that table.
Foreign key: If a field or a combination of fields of one table can be used to uniquely
identify records of another table, then that particular field is known as the foreign key.
Data Definition Language and Data Manipulation Language
What is Data type?
A data type refers to the type of data that will be stored in that particular field. The memory
size of a field varies according to its data type.
Commonly used data types in MySQL
Difference between char and varchar
What is Constraints?
Constraints are the certain types of restrictions on the data values that an attribute can have.
Create database
CREATE DATABASE databasename;
Use database
The use statement selects a specific data type in the database.
USE database_name;
Show databases
SHOW DATABASES will display a list of databases in the MySQL server.
SHOW DATABASES;
Drop database
The DROP DATABASE command is used to drop an existing database.
DROP DATABASE databasename;
Show table
The SHOW TABLE command is used to display the table in the database.
SHOW TABLES;
Create table
The CREATE TABLE is used for creating a table in a database. In the CREATE TABLE
command, the column name and data type must be specified.
CREATE TABLE table_name
( column1 datatype,
column2 datatype,
.... );
Describe table
The DESCRIBE TABLE is commonly written as DESC TABLE or DESCRIBE. This command
is used to retrieve metadata about a table, like field, data type, null, key constraints, etc.
DESCRIBE table_name;
Alter table (add and remove an attribute, add and remove primary
key)
The ALTER TABLE is used to add, delete or modify columns in an existing table.
ALTER TABLE table_name ADD column_name datatype;
Drop table
The DROP TABLE command is used to remove a table permanently from a database.
DROP TABLE table_name;
INSERT INTO Statement
To insert a new record in a table, the INSERT INTO statement can be used. There are two
different ways to insert a value in the table.
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
DELETE Statement
The SQL DELETE statement is used to remove one or more rows from a table based on the
condition using the WHERE clause
DELETE FROM table_name WHERE condition;
SELECT Statement
The SELECT statement is used to display data from a table.
To display all records from the table teacher.
SELECT * FROM teacher;
The WHERE clause is used to filter records based on condition.
SELECT * FROM teacher WHERE Teacher_ID='T100';
SQL Aliases
Aliases are used as a temporary name of the column; it helps to make column names more
readable. An alias is created with the AS keyword during the execution of the query.
SELECT Teacher_ID AS ID, First_Name AS Teacher_Name FROM Teacher;
Distinct clause
The DISTINCT statement is used to retrieve only unique values from the table; it eliminates
duplicate records from the table.
SELECT DISTINCT Department FROM Teacher;
Where clause
The WHERE clause is used to filter records from the table based on the specific condition.
SELECT * FROM Teacher WHERE Salary>50000;
a. The SQL IN Operator
The IN operator allows you to filter multiple values in a WHERE clause.
SELECT * FROM Teacher WHERE Department IN ('IT', 'ENGLISH', 'MATHS');
b. The SQL BETWEEN Operators
The BETWEEN operator select values within a given range.
Programming
SELECT * FROM Teacher WHERE Salary BETWEEN 50000 AND 75000;
c. SQL like Statement
The LIKE operator is used in a WHERE clause to search for a specific value from the column.
There are two wildcards used in conjunction with the LIKE operator:
The percent sign % represents zero, one, or multiple characters.
The underscore sign _ represents one single character.
SELECT * FROM Teacher WHERE First_Name LIKE 'a%';
The SQL order by
The ORDER BY keyword is used to sort the data in ascending or descending order.
SELECT * FROM Teacher ORDER BY Teacher_ID;
Aggregate functions (max, min, avg, sum, count)
max(): The MAX() function returns the largest value from the table.
SELECT MAX(Salary) FROM Teacher;
min(): The MIN() function returns the smallest value from the table.
SELECT MIN(Salary) FROM Teacher;
avg(): The AVG() function returns the average value of the given number in the table.
SELECT AVG(Salary) FROM Teacher;
sum(): The SUM() function is used to find the sum of numbers in the table.
SELECT SUM(Salary) FROM Teacher;
count(): The COUNT() function is used to count the number of rows present in the table.
SELECT COUNT(Teacher_ID) FROM Teacher;