Development Online Course by Udemy, On Sale Here
SQL ServerMySQL-SQL SERVERT-SQLMySQL
An excellent training about Database Design & Development
SQL ServerMySQL-
SQL ServerMySQL-SQL ServerMySQLSQL Server2019,18.71.SQL ServerMySQLSQL Server20171. Windows 10SQL ServerSQLServer2017-SSEI-Dev2. SQL Server Management SSMS-Setup-CHT3. SQL Server Management 1.1-1.Windows 10Justinwu1-2.Justinwu2.3.SQL Server4. SQLSQLDDLcreate database companygouse companycreate table ( int not, char(20) not, char(8) not, char(80), money, int, primary key())create table ( int not, char(20) not, datetime, money, int not, primary key())create table ( int not, char(80) not, primary key(,))create table ( int not, char(20) not, char(80) , int not, primary key(),foreign key() references ())create table ( int not, int not, int, primary key(,),foreign key() references (),foreign key() references ())create table ( int not, char(20) not, char(8) ,char(80)primary key(,),foreign key() references ())SQLDMLinsert into (,) values (1,’3D’,’299′,1)insert into (,) values (6,1,25)insert into (,) values (6,2,250)insert into (,) values (13,4,350)insert into (,) values (15,1,25)insert into (,) values (15,2,350)5.ER DiagramALTER TABLE ADD CONSTRAINT depart noFOREIGN KEY ()REFERENCES ()ON DELETE NO ACTIONON UPDATE NO ACTIONALTER TABLE ADD CONSTRAINT managerFOREIGN KEY ()REFERENCES ()ON DELETE NO ACTIONON UPDATE NO ACTIONALTER TABLE ADD CONSTRAINT depart locFOREIGN KEY ()REFERENCES ()ON DELETE NO ACTIONON UPDATE NO ACTION6. select-Join1. Join(AXB)1 Join R2JoinR1R2Join-INNER JOIN.9,8,8*9=722. INNER JOINselect. from inner join on. = .3. LEFT Outer Join4. RIGHT OUTER JOINRIGHT OUTER JOIN()use companyselect. from RIGHT join on. = .FULL Outer Joinuse companyselect. from full join on. = .5.UNIONselect use company;select * from where like ‘%’ or like ‘%’ ;use company;select * from where ( like ‘%’ or like ‘%’) and >7;use company;select * from where ( like ‘%’ or like ‘%’) and >7 order by desc;use company;select count(*) as, emp. from as emp group by emp.;select count(*),max(emp.),min(emp.),avg(emp.),emp. from as emp group by emp.;use companyselect count(*),max(emp.),min(emp.),avg(emp.),emp. from as emp group by emp.;use companyselect count(*),max(emp.),min(emp.),avg(emp.),emp. from as emp, as depart group by emp.;use companyselect count(*),max(emp.),min(emp.),avg(emp.),emp. from as emp, as depart where depart. = emp. group by emp.;-use company;select count(*),max(emp.),min(emp.),avg(emp.),emp, sum(emp.) from as emp, as depart where depart. = emp. group by emp.;use companyselect count(*),max(emp.),min(emp.),avg(emp.),emp. from as emp, as depart group by emp.;use companyselect count(*),max(emp.),min(emp.),avg(emp.),emp. from as emp, as depart where depart. = emp. group by emp.;-use company;select count(*),max(emp.),min(emp.),avg(emp.),emp, sum(emp.) from as emp, as depart where depart. = emp. group by emp.;insert updateuse companyinsert into (,)select, from; use companyupdate set =*1.10 where in (8,9,10,11,13,15)SQL SERVER 20171.View Create View AScreate view HighSalary Viewwith encryptionasselect * from where >800002.SQL [email protected]@@@rowcount,[email protected]@identify,@@errorT-SQLCast(),convert()[email protected]@y5INTchar3. ififififcasewhiledowhileLoopBooleanB={true, false}truefalsetruefalsetruefalseiftruefalseTruefalse3-1begin end 3-2IFIF [ELSE IF.].[ELSE.]if12if90100A8089B7079C6069D59F828089B5Db id(company)company. true, falseObject id(),3-3if. else if. else3-4CASECASEWHEN THEN [WHEN THEN.].[ELSE.]END3-5while: While begin. ENDifwhileWhile. whiledowhile3-6trycatchT-SQLTRY/CATCHTrycatch.:Begin tryT-SQLEnd tryBegin catchT-SQLEnd catchsp addmessageRaiserror(,)throw, raiserror()4.Stored ProcedureStored ProcedureStored functionCREATE PROCEDURE [@1, @2, ][with {recompileencryptionrecompile, encryption}]ASProcedure5. SQL SERVERSQL SERVERmyfunction();return, exec. exec myprocedure();CREATE PROCEDURE [@,]ASBEGINProcedure. ENDSQL scriptCREATE FUNCTIONRETURNSRETURNStored functionsCREATE function ([@ ,])returns [with(encryptionschemabindingencryption, schemabinding)]ASBEGINfunction. return; ENDCREATE [ OR ALTER ] FUNCTION [ schema name. ] function name ( [ { @parameter name [ AS ][ type schema name. ] parameter data type [ = default ] [ READONLY ] } [ .n ] ] ) RETURNS return data type [ WITH [ .n ] ] [ AS ] BEGIN function body RETURN scalar expression END [ ; ]6.cursorT-SQLcursor. Declare OpenselectFetchCloseDeallocate: Declare cursor[localglobal][forward onlyscroll][fast forwardstatickeysetdynamic][read onlyscroll locksoptimistic]For select Local: GLOBAL: FORWARD ONLY: SCROLL: STATIC, KEYSETDYNAMICSCROLL. FAST FORWARDSTATIC: KEYSET: DYNAMIC: NEXT: FORWARD ONLYNEXTPRIOR: FIRST: LAST: ABSOLUTE n:nRELATIVE n:[email protected]@FETCH [email protected]@CURSOR [email protected]@FETCH STATUS:[email protected]@FETCH STATUS:-1,[email protected]@FETCH STATUS:-2,[email protected]@CURSOR ROWS:n,@@CURSOR ROWS:0,@@CURSOR ROWS:-1,DYNAMICcursorDECLARE cursor name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select statement [ FOR { READ ONLY UPDATE [ OF column name [ .n ] ] } ] [;] Transact-SQL Extended Syntax DECLARE cursor name CURSOR [ LOCAL GLOBAL ] [ FORWARD ONLY SCROLL ] [ STATIC KEYSET DYNAMIC FAST FORWARD ] [ READ ONLY SCROLL LOCKS OPTIMISTIC ] [ TYPE WARNING ] FOR select statement [ FOR UPD
Udemy is the leading global marketplace for learning and instruction
By connecting students all over the world to the best instructors, Udemy is helping individuals reach their goals and pursue their dreams.
Study anytime, anywhere.
Reviews
There are no reviews yet.