DATA BASE SQL

 

             

 

 

 

 

 

 

DATA BASE :-
LAPTOP-KTNGB4Q7\SQLEXPRESS01

 

 

 

 

 

 

 

 

 

 

 

 

  DATE:-13/8/2025 & 15/11/2025

 

DATA BASE:-

 

-          Data base is collection of antity.(Table)

-          All data base related activity used in server explorer window

-          In dot. Net framwork support SQL server management studio

-          The Extention of SQL server is (.mdf) - Microsoft data file

-          There are two types of method is used to work data base environment in dot net Framwork

1.       Connect structure

2.       Disconnect structure

 

HOW TO CREATE DATA BASE IN DOT NET FRAMWORK :-

STEPS:-

Go to solution explorer -> of the root -> add -> new item-> search sql -> select sql server data base option -> name of data base (.mdf) -> add

NOTE:-

all data base location in app data  folder in  dot net (dot net framwork).

 

HOW  TO ACTIVATE DATA BASE ENVIRONMENT :-

-          Data base environment is to be activated using server explorer window.

 

 

 

Data base is collection of NTT(table)

Ex. College database

     Student – roll, name, city

     Faculty- name, designation, subject

 

TYPES OF DATA BASE:-

THERE ARE TWO TYPES OF DATA BASE :-

1.LOCAL DATA BASE

2.GLOBAL DATA BASE (Web database)

 

EXTENTION OF DATA BASE:-

SQL =.mdf

XML= .xml

 

DATA BASE STRUCTURE :-

- data base structure is represent table format.

Roll     name        city

101   mahesh    Nagpur

 

DATA BASE TERMINOLOGY:-

-there are following terms used in data base.

       1     data base field

1         data base record  - all types of data in a data base is called record

2         data base tuple

3         data base query

 

1        data base field

roll name city Is called field.

= ex . roll     name       city

          101    rahul       Nagpur

          102   MAHESh  nashik 

 

2        data base record

all types of data in a data base is called record

 

-types of field :-

1.Single value field

2.multivalue filed

3.null field

     

        1.Single value field

= in this types of field only store one data value

 Ex. Roll no , Aadhar number, ETC

 

        2.multivalue filed

=in this types of field used multiple data record

Ex. Address (office address, recidence address)

 

        3.null field

=in this types cannot required compulsory data

Ex. Mobile number, pan card, Etc

 

3.DATA BASE TUPPLE:-

-complete data base called tupple.

 

4.DATA BASE QUERY :--

-query is representing command.

 

C# and development

 

 

DATA BASE DATA TYPE :-

Following data type used in data type.

1.      Numerical INT

2.      VARCHAR -accept value character or symbol

3.      ANDVARCHAR -accept number & character

4.      Date – accept date

5.      Image

 

 

HOW TO CREATE DATA BASE :-

 

Right click of data base à new data base à name of data base à ok à

DATA BASE QUERY :-

-          Query is set of command is used to apply data base environment

-          All types of query is performed (query editor window)

-          Query editor window is used only server explorer window

STEPS :- 

DOUBLE CLICK  ON data base  -> go to server explorer -> right click on table folder -> and select new query option

 

HOW TO ADD NEW QUERY :-

 

STEPS :-

SELECT YOUR DATA BASE NAME à click new query option  à

HOW TO CREATE NEW TABLE :-

SYNTEX:-

CREATA TABLE NAME(Column datatype)

CREATE TABLE STUDENT(Roll int, name varchar(50));

 

HOW TO DISPLAY TABLE STRUCTURE :-

SYNTEX:-

SP_HELP STUDENT;

 

HOW TO CREATE INSERT QUERY :-

-there are three specific pattern used in insert query.

 

1. INSERT ALL RECORDS

   INSERT INTO TABLENAME  VALUE (DATA )

   NOTE:-   -All data string data value used (‘   ’)

INSERT INTO STUDENT VALUES(101,'MAHESH');

INSERT INTO STUDENT VALUES (102,'RAHUL')

INSERT INTO STUDENT VALUES(103,'MEHUL')

INSERT INTO STUDENT VALUES(104,'AASHU')

INSERT INTO STUDENT VALUES(105,'ABHI')

 

 

2.INSERT PARTICULAR RECORD

  INSERT INTO TABELNAME(COLUMN ) VALUES(DATA        )

 

3.INLINE INSERT :-

Syntex:-

INSERT INTO TABLENAME VALUES(DATA),(DATA),(DATA)----

INSERT INTO STUDENT VALUES(101,'ABHI'),(102,'MEHUL'),(104,'MAHESH')

 

 

HOW TO DISPLAY DATA RECORDS:-

-there are some following methods is used to display data record.

 

1. DISPLAY ALL RECORDS

Syntex:-

SELECT*FROM TABLENAME

SELECT*FROM STUDENT

 

2.DISPLAY PARTICULAR

SYNTEX:-

SELECT COLUMN NAME FROM TABLENAME

SELECT NAME FROM STUDENT

 

3.DISPLAY WITH CONDITION

-in this concept using data base clauses

1. WHERE CLUASE

2.BETWEEN CLUASE

3.GROUP BY

4. ORDERD BY

5. INCLAUSE

6. EXIST CLUASE

 

TASK:-

CREATE A TABLE EMPLOYEE (ID,NAME,city ,state, country, salary )

Insert any `10 records with all operation and perform select operation

 

ANS:-

 

SELECT*FROM WORKER

CREATE TABLE WORKER(id int,name varchar(10),city varchar(10),state varchar(10),country varchar(10),salary decimal(10));

INSERT INTO WORKER VALUES(111,'MAHESH','NAGPUR','MAHARASTRA','INDIA',5000);

INSERT INTO WORKER VALUES(112,'RAHUL','MUMBAI','HARYANA','INDIA',7000);

INSERT INTO WORKER VALUES(114,'MEHUL','PATNA','BIHAR','INDIA',10000);

INSERT INTO WORKER VALUES(115,'ABHISHEK','SHREENAGAR','JAMMU','INDIA',12000);

INSERT INTO WORKER VALUES(116,'ISSHU','AMRITSAR','PUNJAB','INDIA',15000);

INSERT INTO WORKER VALUES(117,'ROHINI','HYDERABAD','TELGANA','INDIA',20000);

INSERT INTO WORKER VALUES(118,'SHUBHAM','DOHA','KATAR','DUBAI',3000);

INSERT INTO WORKER VALUES(119,'SAGAR','BANGLORE','KARNATAKA','INDIA',7000);

INSERT INTO WORKER VALUES(120,'NIDHI','RAIPUR','CHATTISHGAHR','INDIA',12000);

SELECT*FROM WORKER WHERE NAME=SHUBHAM;

SELECT* FROM employee WHERE id BETWEEN 112 AND 119;

SELECT* FROM employee WHERE salary > 10000;

SELECT* FROM employee WHERE id=120;

SELECT* FROM employee WHERE city='BHOPAL';

SELECT* FROM employee WHERE salary<6000;

 

 

 

 

 

DATA BASE QUERY :-

-          Query is set of command is used to perform specific action in a data base table there are following queryies is used to data base.

 

1.       CREATE TABLE QUERY

2.       INSERT QUERY

3.       UPDATE QUERY

4.       DELETE QUERY

5.       SELECT QUERY

 

1 CREATE TABLE QUERY :-

In this query used to create intity in a data base

 

SYNTEX:- 

 

Create table  name(columnname datatype)

 

 

Ex. Create table  student(roll  int, name VARCHAR(50))

 

CREATE TABLE STUDENT(ROLL INT, NAME VARCHAR(50))

 

 

INSERT RECORDS:-

- there are following pattern is used to insert record in  data base.

 

1.       INSERT ALL RECORDS

2.       INSERT PARTICULAR RECORDS

3.       INLINE INSERT

 

4        INSERT ALL RECORDS :-

SYNTEX:-

   Insert  Into Name Values(Data)

EX. 

INSERT INTO STUDENT VALUES(101,'RAHUL')       

INSERT INTO STUDENT VALUES(102,'MAHESH')

INSERT INTO STUDENT VALUES(103,'KARAN')

 

 

     2 INSERT PARTICULAR RECORDS :-

-          IN This concept used to insert records in a particular column.

 

SYNTEX. :-

 

Insert Into Name(Column name )Values(Data);

        EX.  INSERT INTO STUDENT(NAME)VALUES('RAHUL');

 

     3 INLINE INSERT :-

-          IN This concept user can insert data records with the help of single line method.

 

SYNTEX:-

EX.

 

INSERT INTO STUDENT VALUES(201,'RAHUL'),(301,'MAHESH'),(401,'KARAN');

 

 

HOW TO DISPLAY DATA RECORD:-

 

-Display data record in table using select command.

SYNTEX:-

SELECT * FROM STUDENT;

 

 

                           DATA BASE CLAUSE :-

 

Display record with condition :-

 – This concept using database clause

 -  There are following database clause.

-          1. Where clause – Using condition

-          2. Between Clause – Set the range of data.

-          3. Order by clause – Arrange of Records

-          4. Group by clause – Group of similar Data

-          5. Having Clause – Using SQL functions

-          6. IN Clause – Search the multiple Strings.

 

 

1.Where Clause :-

 

SYNTEX :-

SELECT * FROM Student WHERE Roll=101;

 

SELECT * FROM STUDENT WHERE roll=101;

 

2.DISPLAY RECORDS WITH MULTIPLE CONDITIONS :-

SYNTEX:-

SELECT * FROM STUDENT WHERE ROLL=101 AND NAME='RAHUL';

 

 

3.BETWEEN CLAUSE (SET THE RANGE):-

SYNTEX :-

SELECT * FROM STUDENT WHERE ROLL BETWEEN 102 AND 103;

 

4.GROUP BY CLAUSE :-

-Display repeeted data value at once.

SYNTEX:-

SELECT NAME FROM STUDENT GROUP BY NAME;

 

5.ORDER BY CLAUSE :-

-arrange data record (ascending or descending order )

Ascending order- ASC

Descending order- DESC

SYNTEX :-

SELECT * FROM STUDENT ORDER BY ROLL ASC;

SELECT * FROM STUDENT ORDER BY ROLL DESC;

 

6.IN CLAUSE :-

-This clause is used to multiple search fundamental

SYNTEX:-

SELECT * FROM STUDENT WHERE NAME IN ('RAHUL','KARAN');

 

UPDATE QUERY :-

-this query is used to modify table record

SYNTEX:-

UPDATE TABLE NAME SET COLUMN NAME =VALUE WHERE CONDITION

UPDATE STUDENT SET NAME='MOHAN' WHERE ROLL=101;

 

 

DELETE QUERY :- (REMOVE RECORD)

SYNTEX:-

DELETE FROM TABLE NAME WHERE

DELETE FROM STUDENT WHERE ROLL=101;

 

ALL KEYS :-

 

SELECT* FROM student

SELECT * FROM STUDENT WHERE roll=101;

SELECT * FROM STUDENT WHERE ROLL=101 AND NAME='RAHUL';

SELECT * FROM STUDENT WHERE ROLL BETWEEN 102 AND 103;

SELECT NAME FROM STUDENT GROUP BY NAME;

SELECT * FROM STUDENT ORDER BY ROLL ASC;

SELECT * FROM STUDENT ORDER BY ROLL DESC;

SELECT * FROM STUDENT WHERE NAME IN ('RAHUL','KARAN');

DELETE FROM STUDENT WHERE ROLL=101;

UPDATE STUDENT SET NAME='MOHAN' WHERE ROLL=101;

 

 

TASK 1:-

CREATE A TABLE MARKSEET

COLUMN NAME – ROLL, NAME, CITY ,COLLEGE, MARK1,MARK2,MARK3,MARK4,MARK5 , TOTAL , GRADE

LOGIC-

1.       INSERT 10 RECORD

2.       EXCLUDE TOTAL AND GRADE COLUMN (RECORD INSERT NAHI KARNA HAI)

3.       DISPLAY TABLE STRUCTURE

4.       UPDATE TOTAL COLUMN (FORMULA -)

TOTAL =

5.       UPDATE GRADE COLUMN IN FOLLOWING CONDITION

TOTAL > 250 à A GRADE

TOTAL>150<250 à B GRADE OTHERWISE FAILED.

 

6.       UPDATE NAME OF MARKSHEET (MUKESH WHERE ROLL NO. IS 101)

7.       DELETE RECORD FOR MARKSHEEET WHERE ROLL NO IS 101

8.       DISPLAY ALL RECORDS  FOR MARKSHEET TABLE

  WHERE CITY IS NAGPUR,MUMBAI

9.       PERFORM GROUP BY CLUASE IN MARKSEET TABLE

10.   DISPLAY ALL RECORD FOR MARKSHEET TABLE WHERE TOTAL BETWEEN (280 TO 300)

11.   CREATE ANY ONE QUERY IN MARKSHEET TABLE IN YOUR OWN IDEAS(EXCLUDE ASSIGNMENT QUERY)

 

ANS:-

 

--CREATE A TABLE MARKSEET--

SELECT* FROM MARKSHEET;

--INSERT 10 RECORD--

CREATE TABLE MARKSHEET(ROLL INT, NAME VARCHAR(50), CITY VARCHAR(50) ,COLLEGE VARCHAR(50), MARK1 INT,MARK2 INT,MARK3 INT,MARK4 INT,MARK5 INT , TOTAL INT, GRADE VARCHAR(5));

INSERT INTO MARKSHEET VALUES(101,'MAHESH', 'NAGPUR','S.B JAIN',72,78,82,66,75, NULL,NULL);

INSERT INTO MARKSHEET VALUES(102,'RAHUL','INDORE','KAMLA NEHRU',77,65,34,89,91, NULL, NULL);

INSERT INTO MARKSHEET VALUES(103,'PRITHVI','MUMBAI','FIRETECHNIC',76,87,98,34,56, NULL, NULL);

INSERT INTO MARKSHEET VALUES(104, 'Sonal', 'Nashik', 'BBC College', 62, 68, 70, 72, 60, NULL, NULL);

INSERT INTO MARKSHEET VALUES(105, 'Priya', 'Katol', 'XYZ College', 90, 85, 92, 88, 80,NULL, NULL);

INSERT INTO MARKSHEET VALUES(106, 'Kunal', 'Pune', 'MNO College', 40, 45, 50, 48, 42, NULL, NULL);

INSERT INTO MARKSHEET VALUES(107, 'Deepak', 'beed', 'LKZ College', 65, 60, 55, 58, 62, NULL, NULL);

INSERT INTO MARKSHEET VALUES(108, 'Vikas', 'baramati', 'GGT College', 52, 58, 60, 62, 65, NULL, NULL);

INSERT INTO MARKSHEET VALUES(109, 'Amit', 'khadwa', 'NNT College', 70, 65, 75, 80, 72, NULL, NULL);

INSERT INTO MARKSHEET VALUES(110, 'Aashu', 'delhi', 'MNC College', 79, 98, 85, 80, 72, NULL, NULL);

SELECT * FROM MARKSHEET WHERE ROLL=101;

--DISPLAY TABLE STRUCTURE--

SP_HELP MARKSHEET;

--UPDATE TOTAL COLUMN--

UPDATE MARKSHEET SET TOTAL = MARK1+MARK2+MARK3+MARK4+MARK5;

UPDATE MARKSHEET SET GRADE =

    CASE

        WHEN TOTAL > 250 THEN 'A'

        WHEN TOTAL > 150 AND TOTAL < 250 THEN 'B'

        ELSE 'F'

    END;

 

--UPDATE NAME OF MARKSHEET (MUKESH WHERE ROLL NO. IS 101)--

UPDATE MARKSHEET SET NAME='MOHAN' WHERE ROLL=101;

 

--DELETE RECORD FOR MARKSHEEET WHERE ROLL NO IS 101--

DELETE FROM MARKSHEET WHERE ROLL=101;

 

--DISPLAY ALL RECORDS  FOR MARKSHEET TABLE WHERE CITY IS NAGPUR,MUMBAI--

SELECT* FROM MARKSHEET WHERE CITY IN('NAGPUR','MUMBAI');

 

--PERFORM GROUP BY CLUASE IN MARKSEET TABLE--

SELECT NAME FROM MARKSHEET GROUP BY NAME;

 

--DISPLAY ALL RECORD FOR MARKSHEET TABLE WHERE TOTAL BETWEEN (280 TO 300)--

SELECT* FROM MARKSHEET WHERE TOTAL BETWEEN 280 AND 300;

 

--CREATE ANY ONE QUERY IN MARKSHEET--

SELECT TOP 1 *FROM MARKSHEET  ORDER BY TOTAL DESC;

 

_________________________________________________________________________________

 

ALL QUERY :-

 

CREATE TABLE STUDENT(ID INT, NAME VARCHAR(50));

INSERT INTO STUDENT VALUES(101,'XYZ')

INSERT INTO STUDENT VALUES(102,'ABC')

SELECT* FROM STUDENT

UPDATE STUDENT SET NAME='RAMESH' WHERE ID=101;

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

DELETE FROM STUDENT WHERE ID=101

INSERT INTO STUDENT VALUES (108, 'AMIT');

INSERT INTO STUDENT VALUES (109,'SUMIT');

INSERT INTO STUDENT VALUES (103,'RAHUL');

SELECT * FROM STUDENT WHERE NAME IN ('AMIT','SUMIT');

SELECT TOP 2 * FROM STUDENT;

SELECT * FROM STUDENT WHERE ID BETWEEN 103 AND 108;

 

 

ALL KEYS :-

CREATE TABLE student(roll INT,name VARCHAR(50));

INSERT INTO student VALUES(101,'amit');

INSERT INTO student VALUES(102,'rahul');

INSERT INTO student VALUES(103,'rushi');

INSERT INTO student VALUES(104,'tejas');

INSERT INTO student VALUES(105,'mahesh');

SELECT * FROM student

SELECT * FROM student WHERE roll=101;

SELECT * FROM student WHERE roll=101 AND name='amit';

SELECT * FROM student WHERE roll BETWEEN 102 AND 104;

_______________________________________________________________________

 

 

TASK 1:-

CREATE A TABLE EMPLYEE

Column name id , name, city, salary , HRA , DA

Insert any 10 record

Generate 10 query using where clause and between clause .

 

 

________ ]   

________ ]    ANS :-

 

 

 

SELECT * FROM employee

CREATE TABLE employee(id INT,name VARCHAR(50),city VARCHAR(50),salary VARCHAR(50),HRA VARCHAR(50),DA VARCHAR(50));

INSERT INTO employee VALUES(11,'one','nagpur',5000,1000,500);

INSERT INTO employee VALUES(12,'two','pune',6000,1100,600);

INSERT INTO employee VALUES(13,'three','nashik',7000,1200,700);

INSERT INTO employee VALUES(14,'four','amravati',8000,1300,800);

INSERT INTO employee VALUES(15,'five','wardha',9000,1400,900);

INSERT INTO employee VALUES(16,'six','dhule',10000,1500,1000);

INSERT INTO employee VALUES(17,'seven','mumbai',11000,1600,1100);

INSERT INTO employee VALUES(18,'eight','baramati',12000,1700,1200);

INSERT INTO employee VALUES(19,'nine','malegao',13000,1800,1300);

INSERT INTO employee VALUES(20,'ten','chandrapur',14000,1900,1400);

SELECT * FROM employee WHERE name='two';

SELECT* FROM employee WHERE id BETWEEN 12 AND 19;

SELECT* FROM employee WHERE salary > 10000;

SELECT* FROM employee WHERE id=20;

SELECT* FROM employee WHERE city='baramati';

SELECT* FROM employee WHERE DA BETWEEN 1000 AND 1300;

SELECT* FROM employee WHERE salary<6000;

SELECT* FROM employee WHERE city= 'nashik';

SELECT* FROM employee WHERE da>1200 AND salary < 13000;

SELECT* FROM employee WHERE salary>6000 AND salary < 13000;

 

 

DATA BASE VIEW :-

 

-          view is called virtual table is used to store query result.

-          There are following operation is performed data base view.

 

1.       CREATE VIEW

2.       UPDATE VIEW

3.       DISPLAY VIEW

4.       DELETE VIEW

 

1.CREATE VIEW:-

 

SYNTEX:-

CRETE VIEW NAME AS QUERY

CREATE VIEW MY AS SELECT* FROM STUDENT

 

2.DISPLAY VIEW:-

 

SYNTEX:-

SELECT* FROM VIEW NAME

SELECT* FROM MY (View name is My)   

             

 

3.UPDATE VIEW:-

 

SYNTEX:-

ALTER VIEW VIEWNAME AS QUERY

ALTER VIEW MY AS SELECT NAME FROM STUDENT

 

4.REMOVE/ DELETE VIEW:-

 

SYNTEX:-

DROP VIEW NAME

DROP VIEW MY

 

      ALL VIEWS:-

 

SELECT*FROM STUDENT

--CREATE VIEW--

CREATE VIEW MY AS SELECT* FROM STUDENT

--DISPLAY VIEW--

SELECT* FROM MY        

--UPDATE VIEW--

ALTER VIEW MY AS SELECT NAME FROM STUDENT

--REMOVE VIEW--

DROP VIEW MY

 

    HOW TO CREATE BACK UP TABLE:- (duplicate table)

-IN this concept using SELECT* QUERY

 

SYNTEX:-

SEELCT* INTO NAMETABLENAME FROM OLD TABLENAME

--DUPLICATE TABLE--

SELECT* INTO STUDENT1 FROM STUDENT

SELECT* FROM STUDENT1

 

 

HOW TO CREATE NOT NULL COLUMN :-

---CREATE NOT NULL COLUMN--

 

CREATE TABLE DEMO1 (ID INT NOT NULL ,NAME VARCHAR(50))

INSERT INTO DEMO1 VALUES(101,'RAM')

SELECT * FROM DEMO1

INSERT INTO DEMO1(NAME)VALUES ('MAHESH')

 

HOW TO RENAME DATA BASE :-

 

SYNTEX:-

EXEC SP_RENAME ‘OLD TABLE NAEM’, ‘NEW TABLE NAME’

EXEC SP_RENAME 'DEMO1','NEWDEMO1'

SELECT *FROM DEMO1

SELECT* FROM NEWDEMO1

 

HOW TO MATHEMATICAL  EXPRESSION IN QUERY EDITOR WINDOW:-

SELECT 20+30/2 AS RESULT

 

HOW TO DIPSPLAY TOP RECORD IN A DATA BASE TABLE:-

-USING TOP QUERY

SYNTEX:-

SELECT TOP 2* FROM STUDENT

 

 

 ALL USING VIEW :-

 

--DUPLICATE TABLE--

SELECT* INTO STUDENT1 FROM STUDENT

SELECT* FROM STUDENT1

 

--CREATE NOT NULL COLUMN--

 

CREATE TABLE DEMO1 (ID INT NOT NULL ,NAME VARCHAR(50))

INSERT INTO DEMO1 VALUES(101,'RAM')

SELECT * FROM DEMO1

INSERT INTO DEMO1(NAME)VALUES ('MAHESH')

 

--RENAME TABLE NAME--

EXEC SP_RENAME 'DEMO1','NEWDEMO1'

SELECT *FROM DEMO1

SELECT* FROM NEWDEMO1

 

SELECT 20+30/2 AS RESULT

 

 

 

SQL FUNCTION :- *** IMP

-ALL TYPE OF SQL FUNCTION IS USED DATA BASE COLUMN THERE ARE FOLLOWING FUNCTION USED IN SQL .

 

SUM ()

AVG()

MIN()

MAX()

COUNT()            

 

SELECT COUNT (*)FROM STUDENT

 

______________________________________________________________________________

TASK1 :-

Impliment all sql function in student table

1 display max total

2 Min total

3         Sum of total

4         count (number 0f total)

5         Perform top query

6         Perform view operation with where clause

7         Perform view operation with In clause

8         Perform view operation with order by clause

9         Perform rename operations

10     Create a dublicate table with name of newstudent

11     Create any 12 query in your own logic

12     Implementation of check constraint in specific table

13     Implementation of default constraint in specific table

 

ANS:-

 

 

--display max total--

SELECT MAX(TOTAL) AS TOTAL FROM MARKSHEET;

--Min total--

SELECT MIN(TOTAL) AS TOTAL FROM MARKSHEET;

--Sum of total--

SELECT SUM(TOTAL) AS TOTAL FROM MARKSHEET;

--count (number 0f total)--

SELECT COUNT(TOTAL) AS TOTAL FROM MARKSHEET;

--Perform TOP Query--

SELECT TOP 1* FROM MARKSHEET ORDER BY TOTAL DESC;

--            Perform view operation with where clause--

--Perform view operation with In clause --

--Perform view operation with order by clause--

 

--Perform rename operations--

EXEC SP_RENAME 'MARKSHEET','MARKSHEET1'

SELECT *FROM MARKSHEET

SELECT* FROM MARKSHEET1

 

--Create a dublicate table with name of newstudent--

SELECT* INTO NEWSTUDENT FROM MARKSHEET1

SELECT* FROM NEWSTUDENT

 

--any 12 query--

SELECT * FROM NEWSTUDENT WHERE Total > 400;

SELECT * FROM NEWSTUDENT WHERE GRADE = 'A';

SELECT CITY, COUNT(*) AS TOTAL FROM NEWSTUDENT GROUP BY CITY;

SELECT AVG(TOTAL) AS Total FROM NEWSTUDENT;

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

SELECT MAX(MARK1) AS MARK1 FROM NEWSTUDENT;

SELECT * FROM NEWSTUDENT WHERE City IS NULL;

SELECT * FROM NEWSTUDENT WHERE TOTAL BETWEEN 300 AND 450;

SELECT * FROM NEWSTUDENT ORDER BY NAME;

UPDATE NEWSTUDENT SET CITY = 'MUMBAI' WHERE Roll = 105;

DELETE FROM NEWSTUDENT WHERE ROLL = 10;

ALTER TABLE NEWSTUDENT ADD AGE INT;

 

--Implementation of check constraint in specific table--

ALTER TABLE NEWSTUDENT ADD CONSTRAINT CHECKMARK CHECK(Mark1 BETWEEN 0 AND 100)

   

--Implement DEFAULT constraint--

ALTER TABLE NEWSTUDENT ADD DEFAULT 'NAGPUR' FOR CITY;

ALTER TABLE NEWSTUDENT DROP CONSTRAINT DF_CITY;

 

 

__________________________________________________________________________________

 

 

DATA BASE CONSTRAINT:-

 

-Constraint is a set of rules is used to provide condition in a specific column.

- in data base each constraint  is provide specific name

 

Syntex:-

CONSTRAINT NAME CONSTRAINTNAME

 

-There are following type of constraint used in data base.

1. check constraint.

2. default constraint

3. key constraint

4. notnull constraint

 

1.      check constraint :-

 

-          in this type of constraint is used to check condition in specific column.

 

SYNTEX:-

CONSTRAINT NAME CHECK (CONDITION)

 

--CREATE CHECK CONSTRAINT--

 

CREATE TABLE EMPLOYEE(id INT, Sal int, CONSTRAINT MY CHECK(Sal>2000));

INSERT INTO EMPLOYEE VALUES(101,1500);

INSERT INTO EMPLOYEE VALUES(101,5000);

 

 

HOW TO CREATE CONSTARINT IN EXICITING TABLE:-

 

-          IN This concept using alter command  ALTER TABLE

 

SYNTEX:-

 

CREATE TABLE DEMOEMP(ID INT, SAL INT);

 

ALTER TABLE DEMOEMP ADD CONSTRAINT MY1 CHECK(SAL>2000);

INSERT INTO DEMOEMP VALUES(101,15000);

 

 

HOW TO REMOVE CONSTRAINT:-

 

-          IN THIS concept using DROP CONSTRAINT COMMAND.

 

  SYNTEX:-

--REMOVE CONSTRAINT--

 

ALTER TABLE DEMOEMP DROP CONSTRAINT MY1;

INSERT INTO DEMOEMP VALUES(101,1500);

 

 

2.      DEFAULT CONSTRAINT :-

 

-          IN this type of constraint is used to  set default value of specific column.

SYNTEX:-

--default constraint--

CREATE TABLE DEMODEF(ID INT, CITY VARCHAR(50) DEFAULT 'NAGPUR');

INSERT INTO DEMODEF VALUES(101,'PUNE');

INSERT INTO DEMODEF (ID) VALUES(102);

SELECT* FROM DEMODEF

 

3.      NOTNULL CONSTRAINT :-

 

-          In this type of constraint set the NOTNULL data column in a table.  (already done)

 

 

 

_________________________________________________________________________________

 

 

              DATA BASE ALTER:-

-          Update query is basically used to modify data record.

-          If modify table structure than using ALTER query.

 

THERE ARE FOLLOWING IMPLIMENTATION USED IN ALTER COMMAND:-

 

1.       ADD NEW COLUMN

2.       REMOVE DATA BASE COLUMN

3.       RENAME DATA BASE COLUMN

4.       CHANGE DATA TYPE

 

--ADD NEW COLUMN--

SELECT*FROM MARKSHEET

ALTER TABLE MARKSHEET ADD STATE VARCHAR(50);

 

--REMOVE EXISTING COLUMN--

ALTER TABLE MARKSHEET DROP COLUMN STATE

 

--RENAME COLUMN NAME--

EXEC SP_RENAME 'MARKSHEET.COLLEGE','COLLEGENAME','COLUMN'

 

--CHANGE DATA TYPE--

ALTER TABLE MARKSHEET ALTER COLUMN MARK1 VARCHAR(50);

SP_HELP MARKSHEET; --(detail Show)

DATA BASE KEY:- imp***

 

-          Key is special type of column is used to manage complet data base

-          There are some type of key used in data base

 

1.       PRIMARY KEY

2.       UNIQUE KEY

3.       FOREIGN KEY

 

 

1        PRIMARY KEY :-

 

-          THERE ARE TWO PROPERTY USED IN KEYS :-

 

1.       No repeat

2.       No blank 

--CREATE PRIMARY KEY—

 

CREATE TABLE KEYDEMO(ID INT PRIMARY KEY,NAME VARCHAR(50))

INSERT INTO KEYDEMO VALUES (101,'RAHUL');

INSERT INTO KEYDEMO(NAME) VALUES 'MAHESH';

 

 

 

 

2        UNIQUE KEY:-

 

-There are two property used in unique key.

 

1.       NO REPEAT

2.       BLANK

 

CREATE TABLE UNIQUEDB(ID INT UNIQUE, NAME VARCHAR(50));

SELECT*FROM UNIQUEDB;

INSERT INTO UNIQUEDB VALUES(101,'MAHESH');

INSERT INTO UNIQUEDB (NAME) VALUES('MAHESH');

 

 

 

 

CREATE PRIMARY KEY IN A USING CONSTRAINT:-

 

-There are following sintex is used to create primary key using constraint.

SYNTEX:-

ALTER TABLE NAME ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (COLUMNNAME);

ALTER TABLE DEMO5 ALTER COLUMN ID INT NOT NULL;

ALTER TABLE DEMO5 ADD CONSTRAINT MY4 PRIMARY KEY(ID);

 

--------------------------------------------------------------------------------------------------------------------------------------

TASK:-

1.       Implementation of primary key in existing table

2.       Implementation of primary key using constraint method

3.       Implementation of unique key using constraint method

4.       Implementation of unique key using alter method

 

TASK:-

1.       Create a table product

2.       Column name

            Id,name, cost, quantity, total, discount ,finaltotal

Logic:-

1.       Record insert only id, name, cost, quantity

2.       total calculate (cost*qunatity)

3.       provide 5% discount if total is > graeter than 2000 and update discount finaltotal column

4.       provide 2% discount if total is > greater than 1000 and < less than 2000 and update discount and finaltotal column

5.       id is primary key

6.       display some of total

7.        display some of finaltotal

8.       perform any 2 alter query

9.       create check constraint in quantity column and accpet quantity > 5

 

ANS:-

 

CREATE TABLE PRODUCT(ID INT PRIMARY KEY,NAME VARCHAR(50), COST INT, QUANTITY INT, TOTAL INT, DISCOUNT INT, FINALTOTAL INT);

SELECT*FROM PRODUCT;

INSERT INTO PRODUCT VALUES (101,'mahesh',300,6,NULL,NULL,NULL);

INSERT INTO PRODUCT VALUES(102,'rahul',500,8,NULL,NULL,NULL);

INSERT INTO PRODUCT VALUES(103,'karan',5000,9,NULL,NULL,NULL);

INSERT INTO PRODUCT VALUES(104,'abhi',2000,10,NULL,NULL,NULL);

UPDATE PRODUCT SET TOTAL = COST* QUANTITY;

UPDATE PRODUCT SET DISCOUNT = TOTAL * 0.05, FINALTOTAL = TOTAL - (TOTAL * 0.05) WHERE Total > 2000;

UPDATE PRODUCT SET DISCOUNT = TOTAL * 0.02, FINALTOTAL = TOTAL - (TOTAL * 0.02) WHERE Total > 1000 AND TOTAL<2000;

SELECT SUM(TOTAL) AS TOTAL FROM PRODUCT;

SELECT SUM(FINALTOTAL) AS FINALTOTAL FROM PRODUCT;

ALTER TABLE PRODUCT ADD BRAND VARCHAR(30);

ALTER TABLE PRODUCT DROP COLUMN BRAND;

 

 

 

3       FOREIGN KEY :- IMP****

 

-FOREIGN KEY is provide reference between multiple table

2-Foreign Key is used to specific property

*Main Table is used primary key

*Reference table is used NOTNULL column

*Both table are used one common field

There are following syntax is used to foreign key creation

 

SYNTEX:-

 

FOREIGN KEY(COLUMNNAME) REFERENCES TABLENAME(COLUMNNAME)

EX.:-

CREATE FOREIGN KEY BETWEEN STUDENT AND ACCOUNT TABLE :-

STUDENT-(MASTER)

ROLL(PK)   NAME         BRANCH

101             MAHESH    ETC

102             IJJJA             IT

 

ACCOUNT(SUBTABLE)

ROLL(NOTNULL)     FEES     MONTH

101                          2000      JULY

104                          4000      AUG (ERROR)

 

 

--CREATE FOREIGN KEY BETWEEN STUDENT AND ACCOUNT

CREATE TABLE FSTUDENT(ROLL INT PRIMARY KEY, NAME VARCHAR(50));

SELECT*FROM FSTUDENT

INSERT INTO FSTUDENT VALUES(101,'SUMIT')

INSERT INTO FSTUDENT VALUES(102,'BHAVESH')

 

-- CREATE FOREIGN KEY IN FACCOUN TABLE--

CREATE TABLE FACCOUNT(ROLL INT NOT NULL, FEES INT, FOREIGN KEY(ROLL) REFERENCES FSTUDENT(ROLL));

INSERT INTO FACCOUNT VALUES(101,2000);

INSERT INTO FACCOUNT VALUES(104,4000);

 

 

 

DATA BASE UNION, INTERSECT, UNION ALL OPERATION:-

 

1 UNION:-

-          IN This operation union can return common data value at once.

-          Union is used to different entity(table)

 

EX.:-

 

A = [1,2,3]

D = [1,5,6]

A UNION D = [1,2,3,5,6]

 

SINTEX:-

-- PERFORM UNION BETWEEN UTB1 AND UTB2--

SELECT ID FROM UTB1 UNION SELECT ID FROM UTB2;

 

2 INTERSECT,

-In this operation Only display common data value at once.

EX.:-

A = [1,2,3]

B = [1,5,6]

A INTERSECT B=[1]

 

SINTEX:-

--PERFORM INSTERDECT BETWWEN UTB1 AND UTB2

SELECT ID FROM  UTB1 INTERSECT SELECT ID FROM UTB2;

 

3. UNION ALL OPERATION:-

-IT RETURN ALL Combination

SYNTEX;-

--PERFORM UNION ALL OPERATION between UTB1 AND UTB2--

SELECT ID FROM UTB1  UNION ALL SELECT ID FROM UTB2;

 

 

 

--UNION IMPLIMENTATION--

CREATE TABLE UTB1(ID INT, NAME VARCHAR(50));

INSERT INTO UTB1 VALUES (101,'RAHUL');

INSERT INTO UTB1 VALUES (102,'RUTVIK');

INSERT INTO UTB1 VALUES (103,'MADDY');

SELECT* FROM UTB1;

 

CREATE TABLE UTB2 (ID INT,COST INT);

INSERT INTO UTB2 VALUES(101,5000);

INSERT INTO UTB2 VALUES (201,8000);

INSERT INTO UTB2 VALUES (301,6000);

SELECT* FROM UTB2;

 

-- PERFORM UNION BETWEEN UTB1 AND UTB2--

SELECT ID FROM UTB1 UNION SELECT ID FROM UTB2;

 

--PERFORM INTERSECT BETWWEN UTB1 AND UTB2

SELECT ID FROM  UTB1 INTERSECT SELECT ID FROM UTB2;

 

--PERFORM UNION ALL OPERATION between UTB1 AND UTB2--

SELECT ID FROM UTB1  UNION ALL SELECT ID FROM UTB2;

 

 

 

TASK:-

PERFORM UNION , INTERSECT, UNION ALL OPERATION:-

1 MOVIE ASSIGNMENT

2 CUTOMER ASSSIGNMENT

4         STUDENT ASSSIGNMENT

 

---COMPLETED----

 

**CASE QUERY**:- imp

 

 Case query Is used to

-LOGIC

-END AS MASSSAGE (COLUMNNAME)

 

--PERFORM CASE QUERY--

CREATE TABLE CASEDB (ID INT,AGE INT);

INSERT INTO CASEDB VALUES(101,16);

INSERT INTO CASEDB VALUES(102,28);

INSERT INTO CASEDB VALUES(103,68);

SELECT* FROM CASEDB;

--CASE QUERY--

SELECT ID,AGE,

CASE

WHEN AGE<18 THEN 'MINOR'

WHEN AGE>18 AND AGE<60 THEN 'ADULT'

ELSE 'SENIOR'

END AS AGEGROUP

FROM CASEDB;

 

TASK2:-

PERFORM CASE QUERY

1MOVIE TABLE (3)

2STUDENT TABLE (3)

3CUSTOMER TABLE(3)

 

---COMPLETED---

 

NEXT CLASS ASSIGNMENT REVIEW

FOREIGN KEY

UNION ALL

CASE QUERY

 

 

DATA BASE JOINING:-*** IMP

-          Join is a special type of operation where user can access data between multiple table

-          Join all operation to be activated using on keyword

-          Database joining is returning null data value if condition does not match

 

TYPES OF JOINING :-

1)INNER JOIN

2)LEFT OUTER JOIN

3)RIGHT OUTER JOIN

4)FULL OUTER JOIN

5)CROSS JOIN

 

1)INNER JOIN :-

-In This types of joining only match equal condition

NOTE:- All types of join operation is use to table object.

SYNTEX:-

  TABLENAME.COLUMNNAME

EX.

     TABLE1- JOINDB1

    ID                     NAME                 

   101                   RAHUL

   102                   MAHESH

   TABLE2-JOINDB2

   ID                      COST

   101                   500

   102                   200

   103                   500

Perform inner join between JOINDB1 AND JOINDB2.                                    

 

 

--PERFORM INNER JOIN--

CREATE TABLE JOINDB1(ID INT,NAME VARCHAR(50));

INSERT INTO JOINDB1 VALUES(101,'RAHUL');

INSERT INTO JOINDB1 VALUES(102,'MAHESH');

SELECT* FROM JOINDB1;

DELETE FROM JOINDB1; (optional in case add extra data )

CREATE TABLE JOINDB2(ID INT,COST INT);

INSERT INTO JOINDB2 VALUES(101,500);

INSERT INTO JOINDB2 VALUES(102,200);

INSERT INTO JOINDB2 VALUES(103,500);

SELECT* FROM JOINDB2;

DELETE FROM JOINDB2;

--PERFORM INNER JOIN--

SELECT JOINDB1.ID,JOINDB1.NAME,JOINDB2.COST FROM JOINDB1 INNER JOIN JOINDB2 ON JOINDB1.ID=JOINDB2.ID;

 

2) LEFT OUTER JOIN :-

- in this types of join operation only match left side intentity

 

Que. Perform left joining between JOINDB1 AND JOINDB2

         JOINDB1                                        JOINDB2

          ID                                                   ID

         101                                                 101

        102                                                  102

        104

--PERFORM LEFT OUTER JOIN--

SELECT JOINDB1.ID,JOINDB1.NAME,JOINDB2.COST FROM JOINDB1 LEFT OUTER JOIN JOINDB2 ON JOINDB1.ID=JOINDB2.ID;

 

TASK 1:-

Perform inner and left joining

1)Movie table

2)student Table

3)customer table

---COMPLETED---

REVISED TOPIC:-

1.       PAGE CREATION

2.       CONTROL CREATION (TB,BUTTON)

3.       SESSION (QUERYSTRING)

 

 

STORE PROCEDURE:-

-Store procedure is used to store query.

-store procedure basically used to reduce query execution time.

 

HOW TO CREATE STORE PROCEDURE  :-

SYNTEX:-

CREATE PROCEDURE NAME

AS 

BEGIN AS

(QUERY LOGICS)

END;

HOW  TO RUN STORE PROCEDURE:-

STEP:- DATABASES

SYNTEX:-

EXEC PRCODURENAME  

 

 

CREATE PROCEDURE MY1

AS 

BEGIN

SELECT* FROM MOVIE;

END;

 

--EXCUTE PROCEDURE--

EXEC MY1;

 

TASK :-

-          CREATE procedure for following concept.

1.       create table

2.       insert query

3.       select with var clause

4.       Update

5.       primary key

6.       check constraint

7.       foreign key(any one)

8.       union(any one)

9.       Incluase

10.    Alter query (any one)

11.   Case query (any one)

12.   Joining (any one)

13.   Sql function(any one)

 

 

 

CREATE PROCEDURE DREAM11

AS

BEGIN

CREATE TABLE WFO1(ID INT PRIMARY KEY,NAME VARCHAR(50),DESIGNATION VARCHAR(50));

INSERT INTO WFO1 VALUES(101,'SAGAR','CEO');

INSERT INTO WFO1 VALUES(102,'MEHUL','CFO');

INSERT INTO WFO1 VALUES(103,'MAYANK','CMO');

SELECT*FROM WFO1;

 

CREATE TABLE WFO2( ID INT,NAME VARCHAR(50), SALARY INT CONSTRAINT MY CHECK (SALARY > 50000), FOREIGN KEY (ID) REFERENCES WFO1(ID));

INSERT INTO WFO2 VALUES(101,'SAGAR',100000); -- (INSERT QUERY)

INSERT INTO WFO2 VALUES(102,'MEHUL',80000);

INSERT INTO WFO2 VALUES(109,'AASHU',90000);

SELECT*FROM WFO2;

 

--UNION KEY0--

SELECT ID FROM WFO1 UNION SELECT ID FROM WFO2; --(UNION)

SELECT ID FROM  WFO1 INTERSECT SELECT ID FROM WFO2; --(INTERSECT)

SELECT ID FROM WFO1  UNION ALL SELECT ID FROM WFO2; --(ALL OPERATION )

--where CLAUSE--

SELECT * FROM WFO2 WHERE ID = ID;

SELECT * FROM WFO1 WHERE ID=101;

--INNER JOINING--

SELECT WFO1.ID,WFO1.NAME,WFO1.DESIGNATION FROM WFO1 INNER JOIN WFO2 ON WFO1.ID=WFO2.ID;

--OUTER JOINING--

SELECT WFO1.ID,WFO1.NAME,WFO1.DESIGNATION FROM WFO1 LEFT OUTER JOIN WFO2 ON WFO1.ID=WFO2.ID;

--UPDATE QUERY--

UPDATE WFO2 SET SALARY = SALARY + 2000 WHERE ID = ID

--IN CLUASE--

SELECT * FROM WFO2 WHERE ID IN (101, 109)

--ALTER QUERY--

ALTER TABLE WFO2 ADD CITY VARCHAR(50)

----CASE QUERY--

SELECT ID, SALARY,

    CASE

        WHEN SALARY >= 50000 THEN 'HIGH'

        WHEN SALARY >= 30000 THEN 'MEDIUM'

        ELSE 'LOW'

    END AS Salary_Status

    FROM WFO2

--SQL FUNCTION--

SELECT COUNT(*) AS TOTAL_SALARY FROM WFO2;

 

END;

 

 

DOUBTS SESSION & REVIEW:-

-foreign key

-Joining

-constraint

-alter

-screen test (Joining, foreign key, constraint)

 

REMAINING TOPIC:-

-parameterized procedure

-transection

-indexing

 

 

Ye balance hai topic :-

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

PARAMETERIZED STORED PROCEDURE***:-

-in this type stored procedure create a parameter for accepting data 

-all parameter is to be create using @ operator

 

SYNTEX:-

@parameter AS DATA TYPE

EX.:-

1 @ROLL AS INT

2 @NAME AS VARCHAR(50)

 

 

SELECT*FROM MARKSHEET;

EXEC PROINPUT @ROLL1=102, @NAME1='PRIYA'

---------------------------

CREATE PROCEDURE PROINPUT (@ROLL1 AS INT, @NAME1 AS VARCHAR(50))

AS

BEGIN

INSERT INTO MARKSHEET(ROLL,NAME) VALUES(@ROLL1,@NAME1);

END;

 

 

TASK:-

=perform parametrized procedure

-insert query

-update query

-delete query

-select query

 

 

DATA BASE TRANSACTION :-

-Transaction is a process where user can operate multiple query at a time

-transaction is used to specific statement

 1)BEGIN TRANSACTION- (Start transection)

2)COMMIT TRANSACTION –

 

SYNTEX:_

 

BEGIN TRANSECTION

QUERY 1 -----

QUERY2 -----

QUERY3-----

COMMIT TRANSECTION

 

 

--TRANSECTION==

CREATE TABLE TRDB (ID INT,NAME VARCHAR(50));

INSERT INTO TRDB VALUES(11,'MADDY');

INSERT INTO TRDB VALUES(12,'SUNNY');

SELECT*FROM TRDB;

BEGIN TRANSACTION

INSERT INTO TRDB VALUES(13,'TONNY');

UPDATE TRDB SET NAME='RONNY' WHERE ID=12

DELETE FROM TRDB WHERE ID=11

 

COMMIT TRANSACTION

 

 

TASK2:-

-perform transaction property with union, intersect, union all operation

-perform insert, update, delete operation in any one data table

 

REVISED POINT:-

-Create DOTNET application

-Create web form application(aspx)

-create control (textbox…)

-date time….

-Basic quey (insert, delete,update)

 

Tomorrow :-

Connectivity