| 1 | Database | Is organized collection of data where user store their information permanenetly | ||||
| 2 | Database Types > | RDbMS (Relational database Management System) / Client Server Database / Server Side Database | Eg:- MS SQL, MY SQL, ORACLE > Using MS SQL Express Addition, free limit 10GB | MY SQL is open source & ORACLE run in Java | ||
| Desktop Database Program / File Server Database / User Side Database | Eg:- MS Access, MS Excel | |||||
| 3 | Differences b/w Types | Client Server Database | File Server Database | |||
| Secure | Not Secure | |||||
| Multi user can access | Only Single user can access | |||||
| Only part or one webform need to down to edit part of database | Whole database need to down to edit some part | |||||
| User does not acces db file directly as inbetween Master User caller Server makes queues of all requests and let them access db in order | ||||||
| 4 | MS SQL Server > | CLR inbuilt, Can write code too in SQL server | It is release in 1988 for OS II by microsoft | In 1990, New version come for New Technology platform | ||
| 5 | Microsoft Structure Query Language > MS SQL | It is base language for all databases, it is english like language, it is non procedural langauge | Non Procedural > Queries execute one by one, not whole program run by compiling any syntax | |||
| Transit SQL > T-SQL | Store Procedure, Functions, Triggers, Cursors, Indexes, Views | Refers to the programming in SQL | ||||
| 6 | Database Files > | .dbo > System File | Path :- Program Files>MS SQL Server>MSSQL11.Framework>MSSQL>Data | |||
| .mdf > Primary File | ||||||
| .ldf > Log file | ||||||
| .ndf > Secondry file if primary file size exceed | ||||||
| .sdf > mobile database which is is compact and do not have all the functionality | ||||||
| 7 | Data Types > | Numeric > Tiny Int, Small Int, Int, Big Int > Int is alias of Int32 & Big Int alias of Int64 | (0-255/1 Byte), (+-32767/2 Bytes), ( +-2*10^9/4 Bytes), (+-9*10^18/8 Bytes) | A Data type is an characteristic that specifies the type of data an object can hold | ||
| String > Char, Varchar, Varchar (max) > | Fixed Length with 8000 Characters – Std size 1 Byte (takes full memory), Variable Length with 8000 Characters – Std size 1 Byte (takes memory equivlent to characters use and free remaining memory), Variable Length with 2GB size > | for Variable Length If Character = 255 then take 1 Byte Extra, If > 255 then take 2 Bytes Extra | ||||
| Date > Date, Time, Datetime, Timestamp, Year | ||||||
| Unicode String > nChar, nVarchar, nVarchar (max) > | Fixed Length with 4000 Characters, Variable Length with 4000 Characters, Variable Length with 2GB size | |||||
| Binary > binary, varbinary, varbinary (max) | ||||||
| float > | Std size 4 Bytes | In decimal form | ||||
| double > | Std size 8 Bytes | In decimal form | ||||
| 8 | Constraint > | Primary Key > | Must contain unique value, Cannot be Null & Duplicate | It is one for one table, Used to sort, delete, update & insert data | ||
| Unique Key > | Can be Null but not Duplicate | It can be more than one for one table | ||||
| Foreign Key > | Refers to Primary key of other table | It is key used to link multiple tables together | FK not apply to same Parent table multiple times in one table | |||
| 9 | Cluster Index > | Physical | Data is in ascending order with cluster index, Indexes apply in db to do data retreiving fast, for sorting etc | It is fast. | ||
| Logical | ||||||
| 10 | Non Cluster Index > | can be 249 | ||||
| 11 | Tables > | Master Table / Parent Table > | Contains Primary Key / Candidate Key | Less likely to change | ||
| Transition Table / Child Table > | Contains Foreign Key | Freqently Changed | ||||
| 12 | Columns > | Non Sparse column are 1024 nos & Sparse Columns are 30k > | Sparse columns size less by 40% in case of null | Can be use when max columns come null | ||
| 12.1 | Connection to Db > | It is called Provider or engine or Class or Connection > | ODBC | Open Database Connectivity | ||
| OLEDB | Object Linking & Embedding | |||||
| SQL Client | Designed for MS SQL Server | |||||
| Oracle Client | by Oracle | |||||
| 13 | Store Procedure > | It is compiled Object, Stored in database & execute in Db, Performance is better | SQL Injection hacking technique does not apply to Stored Procedure | SP can be nested | ||
| Store Procedure Types > | User Defined > | T-SQL & CLR Store Procedure | ||||
| System Defined > | SP_Renamedatabase, SP_Helpconstraint, SP_Help, SP_AddLogin | |||||
| Store Procedure Parameters > | Input / Output / Input Output | Value Pass / Value Return / Value Pass & Return | Fx return only integer value and SP returns all Values | Fx can use in Query and Expression but store procedure not | ||
| 14 | Diff. b/w Delete, Drop & Truncate | Delete- can delete row one by one, Data can recover after delete, Can apply condition | Drop not reset auto increment column, Can also Remove complete table from Database & also can delete complete Database too | Truncate can delete wholes rows and columns from table, Data cannot be recover, Condition cannot apply, Reset auto increment column | ||
| 15 | Cursors > | It is like array, formed in memory, store query result (Private Area to SQL) | Types :- Static Cusrsor | Dynamic Cursor | Key set Cursor | Steps to make Cursor :- |
| Its just like snapshot | Insertion, Updation, deletion is visible | Rule for this cursor is that primary key is must need to be in table | declare the cursor | |||
| Insertion, Updation, deletion is not visible | it make keys equals to no. of records in table | open the cursor | ||||
| performance wise better | Updation, deletion is visible but Insertion is not as keys are already formed for data in table and new key do not form for new record entered | fetch the cursor | ||||
| drawback – its occupy double memory | it does not occupy double memory | close the cursor | ||||
| data is in table not in cursor as in cursor have just keys for each record | de-allowcate the cursor | |||||
| slow then static cursor | scroll cursor in syntax means first / last, prv/next | |||||
| after, for in syntax, type of cursor to give and if not given then by default it is static cursor | ||||||
| absolute in syntax means record pointer start count from beginning | ||||||
| # | Query > | Create database myfirstdb | ||||
| alter database myfirstdb modify name = kamaldb | ||||||
| alter table tbemp Add empemail varchar(50) | Add column at later satge to existing table | |||||
| drop myfirstdb | ||||||
| use kamaldb | ||||||
| create table tbstu (sturoll int primary key, stuname varchar (10), stuadd varchar(30), stuemail varchar(20) unique) | ||||||
| exec sp_rename ‘tbstu’, ‘tbstudent’ | ||||||
| Insert Data in rows > | insert tbstudent values( 101, ‘ajay’, ‘BCA’, ‘a@gmail.com’) | |||||
| insert into tbB select * from tbA | Create table in tbB first, In this case identity could not apply in tbB | |||||
| insert into tbclass select stuname, stuadd, stuemail from tbstudent > | In this case identity applied in tbclass on sturoll column | |||||
| delete from tbstudent where sturoll=101 | ||||||
| truncate table tbemp | ||||||
| Update tbemp set empsal=4000 where empno=2 | ||||||
| update tbclass set stuadd=’pkl1′ where sturoll=3 | ||||||
| update tbemp set salary=6000 where empno=1 | ||||||
| select * into cssoft.dbo.tbook from practisedb.dbo.tbbook | Copy Table from one Database to another Database | |||||
| Data Display > | Select * from tbstudent | Operator | Meaning | |||
| select stuname, stuclass from tbtudent | ALL | Return true if all comparisons are true | ||||
| select * from tbstudent where sturoll=103 | AND | Return true if both expressions are true | ||||
| select * from tbstudent where stuname=’ajay’ | ANY | Return true if any one of the comparisons is true. | ||||
| AND OR NOT | select * from tbemp where empname=’ajay’ AND empsal=2000 | BETWEEN | Return true if the operand is within a range | |||
| select * from tbemp where empname=’xyz’ OR empsal=2000 | EXISTS | Return true if a subquery contains any rows | ||||
| select * from tbemp where NOT empname=’raj’ | IN | Return true if the operand is equal to one of the value in a list > Either true or both can true | ||||
| Distinct | Select Distinct empsal from tbemp | Does not Display Duplicate value in that column | LIKE | Return true if the operand matches a pattern | ||
| Order By | select * from tbemp order by empsal desc | NOT | Reverse the result of any other Boolean operator. | |||
| select * from tbemp order by empsal asc | OR | Return true if either expression is true | ||||
| select top 3 * from tbemp | Only show top 3 rows | SOME | Return true if some of the expressions are true | |||
| Min Max | select MIN (empsal) as lowestsal from tbemp | as lowestsal is the alias or also use by not writing word ‘as’ | ||||
| select MAX (empsal) as hightestsal from tbemp | ||||||
| select * from tbemp where empsal=(select min (empsal) from tbemp) | ||||||
| select * from tbemp where empsal=(select max (empsal) from tbemp) | ||||||
| select max (empsal) from tbemp where empsal<(select max(empsal) from tbemp) | Only show 2nd highest salary in numeric form | |||||
| select * from tbemp where empsal=(select max (empsal) from tbemp where empsal<(select max (empsal) from tbemp)) | show 2nd highest salary in row form | |||||
| Sub Query | select * from tbemp where empsal=(select max (empsal) from tbemp where empsal<(select max (empsal) from tbemp where empsal<(select max (empsal) from tbemp))) | 1st execute inner query and then outer query | ||||
| COUNT | select COUNT (*) from tbemp | Shows no. of all rows in nemeric form | ||||
| AVG | select AVG (empsal) from tbemp | Show average in numeric form | ||||
| SUM | select SUM(empsal) allsum from tbemp | Show sum in numeric form, allsum is the alias | ||||
| ISNULL | select ISNULL (sem, ‘G.Avg’) sem, ISNULL (sub, ‘S.Ttl’) sub, SUM (Marks) Marks, AVG (avrg) Avrg from semester group by sem, sub with rollup | Sum, Avg, with add of row in sub column and ISNULL is replacement of blank space & group by | Also can use IS NOT NULL | |||
| select * from tbemp where empname like ‘a%’ | Show similar value | |||||
| select * from tbemp where empname like ‘%a’ | ||||||
| select * from tbemp where empname like ‘a__%’ | ||||||
| select * from tbemp where empname like ‘%aj%’ | ||||||
| select * from tbemp where empname like ‘_i%’ | ||||||
| select * from tbemp where empname like ‘a%y’ | ||||||
| select * from tbemp where empname not like ‘a%’ | ||||||
| select * from tbemp where eno in (1,2) | show eno column rows 1 & 2 | |||||
| Between | select * from tbemp where empsal between 2000 and 5000 | |||||
| JOINS | create table tbdsg(dsgcod int primary key, dsgname varchar(20)) | Join Clause (show data from multiple tables) > Inner Join, Left Join, Right, Full Outer Join, self join, equi join, cross join | Inner Join > Show all results matching from both tables | |||
| create table tbdep (dno int primary key, dname varchar(20)) | Left Join > Show all results of Left table & matching from Right Table | |||||
| create table tbemp2 (empno int primary key, ename varchar(20), eadd varchar(20), esal int, edno int, edsgcod int) | Right Join > Show all results of Right table & matching from Left Table | |||||
| add of foreign key | alter table tbemp2 add foreign key (edno) references tbdep (dno) | Full Outer Join > Show all results matched or not from both tables | ||||
| alter table tbemp2 add foreign key (edsgcod) references tbdsg (dsgcod) | Self Join > Join with in the table, called unary relationship | |||||
| select empno, ename, dname, dsgname from tbemp2, tbdep, tbdsg where tbemp2.edno=tbdep.dno and tbemp2.edsgcod=tbdsg.dsgcod | Equi Join which used foreign key also > | Select empno, ename, eadd, esal, edno, edsgcod, dname, dsgname from tbemp, tbdep, tbdsg where edno=dno and edsgcod=dsgcod | ||||
| select empno, ename, dname, dsgname from tbemp2, tbdep, tbdsg where tbemp2.edno=tbdep.dno and tbemp2.edsgcod=tbdsg.dsgcod and tbemp2.edsgcod=101 | AND can use two times | Cross Join > join each row of 1st table with each row of 2nd table | ||||
| select empno, ename, dname, dsgname from tbemp2 e, tbdep d, tbdsg g where e.edno=d.dno and e.edsgcod=g.dsgcod and e.edsgcod=101 | OR with temporary table name called alias | |||||
| select empno, ename, dname from tbemp2 inner join tbdep ontbemp2.edno=tbdep.dno | Inner Join | Show result which match on both side | ||||
| select empno, ename, eadd, esal, edno, dno from tbemp2 left join tbdep ontbemp2.edno=tbdep.dno | Left Join | Show all result of left side & macthed result from right side table | ||||
| select empno, ename, eadd, esal, edno, dno from tbemp2 right join tbdep on tbemp2.edno=tbdep.dno | Right join | Vice -Versa | ||||
| select empno, ename, eadd, esal, edno, dno from tbemp2 full outer join tbdep on tbemp2.edno=tbdep.dno | Full Outer Join | Show all results which matched and not matched | ||||
| select * from tbemp2 cross join tbdep | Cross Join (no condition on cross join) | |||||
| select e.ename Name, m.ename Manager from emp e inner join emp m on e.manid=m.empid | Self Join | |||||
| Group by | select empno, ename from tbemp2 group by ename order by count (empno) desc | Group By | ||||
| select edno, ename, COUNT(*)noe from tbemp2 group by edno, ename | ||||||
| with rollup, new row add | select edno, ename, COUNT(*)noe, SUM(esal)salary from tbemp2 group by edno, ename with rollup | Row add during runtime | ||||
| select edno, ISNULL (ename, ‘S.ttl’)emp2, COUNT(*)nmb, sum(esal)salary from tbemp2 group by edno, ename with rollup | ||||||
| select regcod, regnam, regadd, regemail, cntnam, stanam, ctynam from tbreg, tbcty, tbsta, tbcnt where regctycod=ctycod and ctystacod=stacod and stacntcode=cntcod | ||||||
| delete from subjects DBCC CHECKIDENT(subjects,RESEED,0) | For Resetting Identity of Auto Increment column | |||||
| Offset & Fetch | SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; | Skip & Fetch Rows limiter by OFFSET & FETCH | ||||
| < > | Can also be use for Not Equal to Sign | |||||
| ALL Query | SELECT first_name, last_name, salary FROM employees WHERE salary >= ALL (SELECT salary FROM employees WHERE department_id = 8) ORDER BY salary DESC; | EXIT Query > | SELECT first_name, last_name FROM employees e WHERE EXISTS( SELECT 1 FROM dependents d WHERE d.employee_id = e.employee_id); | Group by with Inner Join, Max, Min & AVG > | SELECT e.department_id, department_name, MIN(salary) min_salary, MAX(salary) max_salary, ROUND(AVG(salary), 2) average_salary FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY e.department_id; | |
| ANY Query | SELECT first_name, last_name, salary FROM employees WHERE salary > ANY(SELECT AVG(salary) FROM employees GROUP BY department_id) ORDER BY first_name , last_name; | Group by Query with HAVING > | SELECT e.department_id, department_name, COUNT(employee_id) headcount FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY e.department_id HAVING headcount > 5 ORDER BY headcount DESC; | UNION > No Duplicate Results | SELECT first_name, last_name FROM employees UNION SELECT first_name, last_name FROM dependents ORDER BY last_name; | |
| Pivot table > Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. | Correlated query > a correlated subquery (synchronized subquery) is uses values from the outer query. Because the subquery may be evaluated once for each row processed by the outer query, it can be slow. | Understanding SQL by Martin Groover | ||||
| String qry=”select * from tbbook where bookid in( “+st+” )”; | st concadinate with query qry | |||||
| More > | select edno, SUM(esal) Salary, COUNT (*) NOE from tbemp2 group by edno | Column used in Group by must be also in select statement, rest in select are agregate fx | Aggretion fx – value of multiple rows grouped togather forming single value | |||
| select edno, ename, SUM(esal) Salary, COUNT (*) NOE from tbemp2 group by edno, ename with rollup | Data Summarize by adding row / etc with rollup, cuve, compute, compute by | rollup is always use with group by | ||||
| select edno, ISNULL(ename, ‘S.Ttl’) Name, COUNT (*) NOE from tbemp2 group by edno, ename with rollup | ||||||
| select ISNULL(dname, ‘G.Ttl’) DepNm, ISNULL(ename, ‘S.Ttl’) Name, SUM(esal) Salary, COUNT (*) NOE from tbdep, tbemp2 group by dname, ename with rollup | ||||||
| select case when (GROUPING (dname)=1) then ‘G.Ttl’ else dname end dname, case when (GROUPING(ename)=1) then ‘S.Ttl’ else ename end ename, SUM(esal) esal from tbdep, tbemp2 where edno=dno group by dname, ename with rollup | grouping always use with rollup | |||||
| MY SQL :-> | ||||||
| DML > | Data Manipulation Language | DDL > | Data Definition Langauge | |||
| Select, Update, Delete, Insert Into | Create Database, Alter Database, Create Table, Alter Table, Drop Table, Create Index, Drop Index | |||||
| Reserved Keywords > | ACCESSIBLE ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE BEFORE BETWEEN BIGINT BINARY BLOB BOTH BY CALL CASCADE CASE CHANGE CHAR CHARACTER CHECK COLLATE COLUMN | CONDITION CONSTRAINT CONTINUE CONVERT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR DATABASE DATABASES DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DEC DECIMAL DECLARE DEFAULT DELAYED DELETE DESC DESCRIBE DETERMINISTIC | DISTINCT DISTINCTROW DIV DOUBLE DROP DUAL EACH ELSE ELSEIF ENCLOSED ESCAPED EXISTS EXIT EXPLAIN FALSE FETCH FLOAT FLOAT4 FLOAT8 FOR FORCE FOREIGN FROM FULLTEXT GRANT | GROUP HAVING HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND IF IGNORE IN INDEX INFILE INNER INOUT INSENSITIVE INSERT INT INT1 INT2 INT3 INT4 INT8 INTEGER INTERVAL INTO IS | ITERATE JOIN KEY KEYS KILL LEADING LEAVE LEFT LIKE LIMIT LINEAR LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK LONG LONGBLOB LONGTEXT LOOP LOW_PRIORITY MASTER_SSL_VERIFY_SERVER_CERT MATCH MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND MOD MODIFIES NATURAL NOT NO_WRITE_TO_BINLOG NULL NUMERIC ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUT OUTER OUTFILE PRECISION PRIMARY PROCEDURE PURGE RANGE READ READS READ_WRITE REAL REFERENCES REGEXP RELEASE RENAME REPEAT REPLACE REQUIRE RESTRICT RETURN REVOKE RIGHT RLIKE SCHEMA SCHEMAS SECOND_MICROSECOND SELECT SENSITIVE SEPARATOR SET SHOW SMALLINT SPATIAL SPECIFIC SQL SQLEXCEPTION SQLSTATE SQLWARNING SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL STARTING STRAIGHT_JOIN TABLE TERMINATED THEN TINYBLOB TINYINT TINYTEXT TO TRAILING TRIGGER TRUE UNDO UNION UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USING UTC_DATE UTC_TIME UTC_TIMESTAMP VALUES VARBINARY VARCHAR VARCHARACTER VARYING WHEN WHERE WHILE WITH WRITE XOR YEAR_MONTH ZEROFILL | |
| Operators > | INTERVAL BINARY, COLLATE ! – (unary minus), ~ (unary bit inversion) ^ *, /, DIV, %, MOD -, + <<, >> & | = (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN BETWEEN, CASE, WHEN, THEN, ELSE NOT &&, AND XOR ||, OR = (assignment), := | Server related commands > | Program Description mysqld This is the MySQL server, which is the main program for MySQL mysqld_safe A server startup script which attempts to start mysqld. This is the recommended method for starting the MySQL server on Unix as it provides restart and logging mechanisms mysql.server A server startup script used on systems that use System V-style run directories that contain scripts that start system services for particular run levels. This script evenually calls mysqld_safe to start the server mysqld_multi This program is used to manage multiple mysqld processes. It can be used to start or stop servers, or report their current status comp_err This program creates the errmsg.sys file for mysqld and is used to determine the error message to display for different error codes mysq_install_db This program is used to initialize the MySQL data directory and creates the necessary system tables mysql_secure_ installation Improves the security of MySQL by: Allows setting the password for root accounts Allows removal of root accounts that are accessible from outside the local host Allows removal of anonymous user accounts Allows removal of the test database mysql_tzinfo_ to_sql Allows the loading of time zone tables in the MySQL database mysql_upgrade Allows checking of tables in all databases for incompatibilities with the current version mysql This is the MySQL command-line tool mysqladmin This program is a client used to perform administrative operations. It can be used to check the server’s status, configuration, manage tables, etc mysqlcheck This program is a client that can be used to perform table maintenance by analyzing, optimizing, or repairing them mysqldump Used to dump a MySQL database for backup or transfer to another server mysqlimport Used to import data into the MySQL database mysqlshow A client program that can be used to show databases, tables, table columns, or indiexes. mysqlaccess This program is a diagnostic tool that checks the access privileges for a host name, user name, and database combination mysqlbinlog This program is used to display the contents of the binary log files contents in text format mysqlbinlog This program is used to display the contents of the binary log files contents in hex dump format | |||
| MY SQL Queries > | create database kamalmysql | |||||
| drop database payroll | ||||||
| show databases | Show list of all databases | |||||
| use kamalmysql | ||||||
| create table tbemp(eno int primary key, ename varchar(20), eadd varchar(20), esal int) | ||||||
| drop table tbstu | ||||||
| show tables | ||||||
| describe tbemp | Show table definition / architecture | |||||
| delete from tbemp where eno = 4 | Deleta data by primary key if have | |||||
| alter table tbstu add column stufee int | ||||||
| alter table tbstu change stuname stunam varchar(20) | Change column name | |||||
| alter table tbstu drop column stumisc | drop column | |||||
| Functions | MY SQL has many functions, as almost same as MS SQL | |||||
| select * from tbemp | ||||||
| select * from tbemp where ename = ‘komal’ | quotation marks required in case char data type | |||||
| select * from tbemp where esal = 40000 | ||||||
| select count(*) records from tbemp | records is the alias | |||||
| FLOOR(DATEDIFF(‘2016-01-01’, hire_date) / 365) YOS | YOS – Year of Service, This returns no. of Year by minus from hire_date. CURRENT_DATE can also be use | |||||
| SELECT employee_id, first_name, last_name FROM employees ORDER BY first_name LIMIT 5 OFFSET 3; | OFFSET means skip these much of rows from starting & Limit means, from where ever it start, show first these much of rows result only | |||||