Database is used to create multiple tables which help in storing data. First you create Database, then you create your table, then you define your coloumn names and their data types. After that you can filter your results applying multiple queries.
- string (CHAR, VARCHAR, BINARY, VARBINARY etc)
- numeric (INT, INTEGER, FLOAT, DECIMAL, DOUBLE, BIGINT, MEDIUMINT, BOOLEAN, BOOL etc)
- Date and Time (DATE(format: YYYY-MONTH-DATE), DATETIME(format: YYYY-MM-DD hh::mm::ss), TIME(format: hh::mm::ss), YEAR)
CREATE DATABASE student;
After running this command you will see student database in schema tab.
Before creating table, you must choose database in which you want to create table.
myDatabase > right click > set as Default Schema
Use following query and run it.
USE student;
CREATE TABLE StudentRecord(
id INT, #coloumnName coloumnDtatype
name VARCHAR(50),
birth_date DATE,
phone VARCHAR(12),
gender VARCHAR(1)
);
Method-1: Click on highlighted button
Method-2: Run following query
SELECT * FROM student;
DESCRIBE myTable;
Once you create a row or table or database, do not re-run that command.
If you do not provide any value for any cell, it will put NULL there.
INSERT INTO studentrecord VALUES (1,"Amna Azam", "2002-04-25", "13250377190","F");
INSERT INTO studentrecord VALUES (2,"Arshia Azam", "2005-08-02", "13250377190","F");
INSERT INTO studentrecord VALUES (3,"Zain Azam", "2007-08-24", "13250377190","M");
if you want to create multiple rows in one command in sql:
INSERT INTO studentrecord VALUES
(1,"Amna Azam", "2002-04-25", "13250377190","F"),
(2,"Arshia Azam", "2005-08-02", "13250377190","F"),
(3,"Zain Azam", "2007-08-24", "13250377190","M");
You apply these constraints while creating tables
- if you want that all entries of one coloumn must be filled/provided, then you will use NOT NULL constraint.
- if you want that one coloumn must have values fulfilling any condition, then you will use CHECK(condition) constraint.
- if you want to place all values unique in any coloumn, you will use UNIQUE constraint.
- if you want to put any default value in any coloumn when user leave any cell empty, you will use DEFAULT CONSTRAINT.
CREATE TABLE test(
id INT UNIQUE NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL CHECK(age>=20),
city VARCHAR(12) NOT NULL DEFAULT ("Okara")
);
if you want to display any two coloumns named as name and gender, you will run following command:
SELECT name,gender
FROM studentrecord;
if you want to display all coloumns, you will run following command:
SELECT *
FROM studentrecord;
SELECT name AS "Student Name", gender AS Gender
FROM studentrecord
SELECT * FROM studentrecord
WHERE gender="F"
SELECT * FROM studentrecord
WHERE age >= 20
SELECT * FROM studentrecord
WHERE city != "Agra" #WHERE city <> "Agra"
SELECT id, name FROM studentrecord
WHERE gender = "F" AND age >= 10 AND age <= 20
SELECT id, name FROM studentrecord
WHERE id = 15 OR id > 10 OR gender = "F"
SELECT id, name FROM studentrecord
WHERE NOT gender = "F" # WHERE gender != "F"
It is equvialent to OR operator
SELECT * FROM studentrecord
WHERE id IN(3,4,5) # WHERE id = 3 OR id = 4 OR id = 5
SELECT * FROM studentrecord
WHERE id NOT IN(3,4,5) # All values except 3,4,5
For range purposes
SELECT * FROM studentrecord
WHERE id BETWEEN 1 AND 3
SELECT * FROM studentrecord
WHERE id NOT BETWEEN 1 AND 3
SELECT * FROM studentrecord
WHERE name LIKE "A%" # "%" sign represents zero, one or multiple characters.
SELECT * FROM studentrecord
WHERE name LIKE "__n%" # where name has n at third position, there are 2 characters before 'n' and after n there may b 0 or multiple characters.
SELECT * FROM studentrecord
ORDER BY name DESC # By default id it ASC
SELECT * FROM studentrecord
ORDER BY name, age DESC #if name has same order then order the results on the base of age coloumn
If you want to print all unique data of ONE COLOUMN(unique entries/unique cells of one coloumn), You will use DISTINCT.
SELECT DISTINCT gender FROM studentrecord # F, M
SELECT name FROM studentrecord
WHERE gender IS NULL #show record where gender is not provided by student.
SELECT name FROM studentrecord
WHERE gender IS NOT NULL #show record where gender is provided by student.
To display number of first rows.
SELECT * FROM studentrecord
LIMIT 3 # Display first 3 rows.
SELECT * FROM studentrecord
LIMIT 2,8 # display 8 rows from third row (after first 2 rows)
SELECT COUNT(name) FROM studentrecord # how many records/rows you have for name.
SELECT COUNT(*) FROM studentrecord # Display count of rows
SELECT MAX(salary),name FROM studentrecord # Display maximum salary with his name
SELECT MIN(salary),name FROM studentrecord # Display min salary with his name
SELECT SUM(salary) FROM studentrecord # Display sum of salaries
SELECT AVG(salary) FROM studentrecord # Display average of salary
It is used for comparison. it is different from IN operator. IN operator is only used for equality but ANY operator can be used with any type of logical operator.
SELECT *
FROM employee
WHERE salary > ANY (2000, 3000, 4000); # where salary>2000 or >3000 or >4000
SELECT *
FROM employee
WHERE salary IN (2000, 3000, 4000); # where salary ==2000 or ==3000 or ==4000
returns TRUE if ALL of the subquery values meet the condition.
SELECT *
FROM employee
WHERE salary > ALL (2000, 3000, 4000); # where salary>2000 and >3000 and >4000
UPDATE studentrecord
SET name = "Ahmed", age = 20
WHERE name = "Zain Azam";
Whenever you use Rollback command it removes all insert, update and delete commands whichever your have used. To save these commands you user commit. Where you use commit command, it saves all the CRUD commands before Commit, but after that rollback works and removes all update, create or delete commnad.
UPDATE studentrecord
SET name = "Arooj Fatima"
WHERE name = "Amna Azam";
SELECT * FROM studentrecord
COMMIT;
UPDATE studentrecord
SET name = "Ahmed"
WHERE name = "Zain Azam";
SELECT * FROM studentrecord
ROLLBACK;
UPDATE studentrecord
SET name = "Azam Ali"
WHERE name = "Zain Azam";
SELECT * FROM studentrecord
DELETE FROM studentrecord
Where id = 3
# To delete all rows from table
DELETE FROM studentrecord
- Primary key has non-null and unique data.
- One table can have primary key constraint on only one coloumn.
CREATE TABLE myTable(
id int UNIQUE NOT NULL AUTO_INCREMENT,
name varchar(50),
age int NOT NULL,
PRIMARY KEY(id)
);
If you have created table:
ALTER TABLE studentrecord
ADD PRIMARY KEY(id);
- Foreign key is used to link 2 tables.
- Foreign key in first table contains all those values that exist in second table's primary key.
- One table can have multiple foreign keys.
CREATE TABLE myTable(
id int UNIQUE NOT NULL AUTO_INCREMENT,
name varchar(50),
age int NOT NULL,
city int NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(city) REFERENCES city(cid)
);
If you haver created table:
ALTER TABLE myTable
ADD FOREIGN KEY(city) REFERENCES City(cid)
- flan cheez has flan cheez.
- flan cheez belongs to flan cheez.(FK)
It shows all those rows of both table where Foreign key and Primary Key get equal.
SELECT P.id, P.name, P.percentage, P.gender, C.cityName
FROM personaltable AS P
INNER JOIN citytable AS C
ON P.city = C.id
WHERE C.cityName = "Lahore"
It shows all the records Where both tables get equal and first table record where they are not equal. Since it is not possible to have such rows in first table which are not in second table. So firstly, you delete foreign key constraint and then you can insert such value in coloumn which is not in primary key of first table. In that case You get NULL values in second table.
After these steps, you can insert values in FK coloumn which do not belogn to second table's PK.
SELECT *
FROM personaltable AS P
LEFT JOIN citytable AS C
ON P.city = C.id
It shows all the records Where both tables get equal and second table record where they are not equal.
Here You do not need to have foreign key in one table and primary key in second table to link those tables.
SELECT *
FROM personaltable
CROSS JOIN citytable
# Suppose You have three tables table1, table2 and table3.
# table1 has two foreign keys for table2 and table3.
# You want to display data against foreign keys
SELECT *
FROM table1
INNER JOIN table2
ON table1.foreignkey1 = table2.primaryKey
INNER JOIN table3
ON table1.foreignkey2 = table3.primaryKey
GROUP BY statement groups records against common values in coloumn and returns one record for each group(Here you apply aggregate function on each group).
# PUCIT, pharmacy, PUCAD, ART etc are some departments
# in which different teachers teach at different salaries.
# you have to display the name of that teacher which has
# MAX salary in his department.
SELECT MAX(teacher_Salary)
FROM employee_table
GROUP BY department_Name
# KIS KIS CITY SE KITNY STUDENTS ATAY HEN?
SELECT COUNT(S.name)
FROM studentTable AS S
INNER JOIN cityTable AS C
ON S.city = C.cid
GROUP BY C.city
# Aik manager has multiple employees. You have to display
# count of empolyees against each Manager.
SELECT COUNT(E.EmployeeName)
FROM EmplyeeTable AS E
INNER JOIN ManagerTable AS M
ON E.Mid = M.id
GROUP BY M.ManagerName
It applies condition on aggregate function (output of grouped result).
SELECT AVG(S.Fees)
FROM studentTable AS S
INNER JOIN cityTable AS C
ON S.city = C.cid
GROUP BY C.city
HAVING AVG(S.Fees)>5000;
By using indexes we can rapidly speed up queries. let suppose you have 1M records in table then any query will take from 4 to 5 seconds to execute but by creating indexes, this query can run only in few miliseconds. These indexes just work like indexes in book (which help in finding any topic fastly). Creating index may take time in few mintues.
# this query will execute in 4 to 5 seconds.
SELECT COUNT(*)
FROM tableName
WHERE firstName="Amna"
CREATE INDEX firstName_idx
ON tableName(firstName)
# now this query will take few mili seconds to execute because we have created index against firstName coloumn.
SELECT COUNT(*)
FROM tableName
WHERE firstName="Amna"
Key is one of the attributes of table
The key which will contain different values(UNIQUE values) in one coloumn. These candidate keys are not enough to identify unique record(bcz it can be null)
The candidate key which will be UNIQUE + NOT NULL, will be called primary key. Each PK is CC but Each CC is not primary key.
Super set of candidate key is Super key. Candidate key(may call it super key(minimal)) can uniquely identify any row but when you add any key(s) with it, it become Super key.
when you use two coloumns to uniquely indentify your records, it is called composite. When two coloumns are treated as primary key it is called composite key.
CREATE TABLE Person(
firstName VARCHAR(50),
lastName VARCHAR(50),
.
.
.
.
PRIMARY KEY(firstName,lastName)
)
Normalization in SQL is the process of organizing data in a relational database to minimize data redundancy and improve data integrity. There is two level redundancy:
- Row level
- Coloumn Leve;
Row level redundancy can be removed using PK.
Coloumn level redundancy can introduce three types of errors:
- Insertion anomoly
- deletion anomoly
- updation anomoly
For example you have one table in which you have student and his course with SID and CID, Univesirty has introduced a new course but you cannot add it in table because you have PK student ID but you have no any student.
For example you have one table in which you have student and his course with SID and CID, You have to delet student with S1D=2, but removing only student info will remove all his course info as well.
For example you have one table in which you have student and his course with SID and CID, When you will try to update only one cell it will update all those cells which will fill that criteria.
First Normal Form (1NF) in SQL is the first step in the normalization process. It requires that each column in a table contain only atomic values.
After following 1NF:
Table should be in first normal form. All the non prime attributes should be fully dependent on the primary key/There should be no partial dependency in table/The non prime attribute(location) should not be the part of the candidate key(storeID). If non prime attribute is determined by part of the candidate, it is called partial dependency.
All non prime attributes must be dependent on the primary key and not on each other.