SQL
Basic syntaxes AND concepts of SQL
Structure of the syntax:
SET sql_mode = only_full_group_by; (Before running group by function, run this syntax)
SET sql_mode = ‘ ’; To remove above setting.
Ø SELECT * FROM <table> WHERE <condition> GROUP BY <column_name> ORDER BY <column_name> ;
Ø SELECT * FROM <table> GROUP BY <column_name> HAVING <aggregate_function> OREDR BY <column_name> <key_word> ;
Ø SELECT * FROM <table> GROUP BY <column_name> HAVING <aggregate_function> OREDR BY <column_name> <key_word> LIMIT <int>;
Creating databse:
CRAETE TABLE Student(
student_name VARCHAR(100) NOT NULL,
student_gender CHAR(1) NOT NULL,
mail_id VARCHAR(100) UNIQUE,
mob_num BIGINT UNIQUE
);
DESCRIBE student;
DML IN DBMS:
CREATE DATABASE DML;
USE DML;
TBALE 1:
CREATE TABLE GL_STUDENTS
(
GL_CODE INT PRIMARY KEY,
SNAME VARCHAR(50),
GRAD_MARK INT,
EMAIL VARCHAR(30) UNIQUE,
MOB_NUMBER CHAR(10) NOT NULL UNIQUE,
LOCATION VARCHAR(30),
CONSTRAINT GL_STUDENTS_CHK CHECK(LOCATION IN (‘MUMBAI’, ‘PUNE’, ‘CHENNAI’, ‘GURGAO’))
);
TABLE2:
CREATE TABLE SCORES
(
GL_CODE INT PRIMARY KEY REFERENCES GL_STUDENTS (GL_CODE),
MODULE1 INT DEFAULT 0,
MODULE2 INT DEFAULT 0,
MODULE3 INT DEFAULT 0
);
Because, this PRIMARY KEY is FOREIGN KEY for first table. If we forgot to mention this as foreign key, we are just assigning it as a FOREIGN KEY. It must be before inserting values.
ALTER TABLE SCORES ADD FOREIGN KEY (GL_CODE) REFERENCES GL_STUDENTS (GL_CODE); [A]
If we want to get some restrictions on any column. we even can alter the situation.
ALTER TABLE SCORES ADD CHECK (MODULE1 > 60);
Now this will allow marks to MODULE3 which are greater than 60.
Like this, we can alter table as many possible times.
ALTER TABLE TRANSACTION ADD UNIQUE (IFSC_CODE);
TABLE 3:
CREATE TABLE GL_PLACEMENTS
(
GL_CODE INT ,
COMPANY VARCHAR(30),
STATUS VARCHAR(10),
CONSTRAINT GL_PLACEMENTS_PK PRIMARY KEY (GL_CODE, COMPANY),
CONSTRAINT GL_PLACEMENTS_FK FOREIGN KEY (GL_CODE) REFERENCES GL_STUDENTS (GL_CODE)
);
TABLE 3 has some uncertainties to declare its primary key, because none of the column is singularly capable to become a primary key. At least two columns are capable to become a primary key. Hence we declare 2 columns as primary key. These keys are called composite key. Here in above table, GL_CODE & COMPANY is set as primary key.
GL_CODE is still running from all tables, which is even referred here as a primary key. So to make a link of this TABLE 3 with previous, we just make is referential key (FOREIGN KEY).
UPDATING DML:
Without breaking any rule defined above, inserting vales will be applicable, otherwise error will be thrown up.
After making tables, now it is not possible to violet any table. We cannot update or delete the table, whether it would be parent table or child table. Hence there must be any way to do so, at least on parent table. Hence we do certain tasks:
We want a system such that, if I delete something from parent table, automatically child table respective should also be get deleted.
ALTER TABLE SCORES DROP FOREIGN KEY SCORES_IBFK_1;
Now [A] got affected. Now command it to allow the changes. Still the table and column do exists, but the constraint FOREIGN KEY is removed.
ALTER TABLE SCORES ADD FOREIGN KEY (GL_CODE) REFERENCES GL_STUDENTS(GL_CODE) ON UPDATE CASCADE;
Or just do initially:
CREATE TABLE SCORES_1
(
GL_CODE INT PRIMARY KEY REFERENCES GL_STUDENTS (GL_CODE) ON UPDATE CASCADE ON DELETE CASCADE,
MODULE1 INT DEFAULT 0,
MODULE2 INT DEFAULT 0,
MODULE3 INT DEFAULT 0
); [B]
FOLLOWINGS ARE THE METHODS TO DO SO:
· CREATED TABLE
· INSERTED VALUES
· CREATED ANOTHER TABLE WITH CASCADE CLAUSE LIKE [B].
· INSERTED THE VALUES.
· NOW UPDATED THE PARENT TABLE. CHILD TABLE AUTOMATICALLY GOT UPDATED.
EXAMPLE:
CREATE TABLE A1
(
RN INT PRIMARY KEY ,
NAME VARCHAR(10),
AGE INT NOT NULL CHECK (AGE>20)
);
CREATE TABLE B1
(
RN INT PRIMARY KEY REFERENCES A1(RN) ON UPDATE CASCADE ON DELETE CASCADE,
MARKS INT CHECK (MARKS BETWEEN 0 AND 100),
FOREIGN KEY (RN) REFERENCES A1 (RN) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO A1 VALUES (1, ‘ASAD’, 25);
INSERT INTO B1 VALUES (1, 45);
SET SQL_SAFE_UPDATES=0;
UPDATE A1 SET RN= 4 WHERE RN=1;
DELETE FROM A1 WHERE RN=4;
SELECT * FROM B1;
