Page 2 :
for more updates visit: www.python4csip.com, , SQL – Structured Query Language,  Is a language that enables you to create and operate, on relational databases,  It is the standard language used by almost all the, database s/w vendors.,  Pronounced as SEQUEL,  Original version was developed by IBM’s Almanden, Research Center,  Latest ISO standard of SQL was released in 2008 and, named as SQL:2008, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 5 :
for more updates visit: www.python4csip.com, , Literals,  It means the fixed value or constant value. It may be of, character, numeric or date time type.,  Character and date/time literals are always in single, quotation marks whereas numeric literals must be, without single quotation marks,  For example – ‘Virat’, 12, 12.56, ‘04-20-2018’,  Date and time values are always in the format, YYYY-MM-DD, HH:MI:SS,  Special character like quotes are always written be, preceding it back-slash(\). For example if we want to, store value as Tom’s Cat then it should be written as, Tom\’s Cat, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 6 :
for more updates visit: www.python4csip.com, , Data Type,  Means the type of value and type of operation we, can perform on data. For example on numeric value, we can store numbers and perform all arithmetic, operations and so on.,  MySQL support three categories of data types:,  Numeric,  Date and time,  String types, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 7 :
for more updates visit: www.python4csip.com, , Numeric Data Types, Data type, , Description, , INT, , Numbers without decimal. Store up to 11 digits. -2147483648 to 2147483647, , TINYINT, , Small integer value between 0 – 255 (4 digits), , SMALLINT, , More than TINYINT between -32768 to 32767 (5 digit), , MEDIUMINT, , Integer values up to 9 digits, , BIGINT, , Very large integer value up to 11 digits, , FLOAT(M,D), , Real numbers i.e. number with decimal. M specify length of numeric value, including decimal place D and decimal symbol. For example if it is given as, FLOAT(8,2) then 5 integer value 1 decimal symbol and 2 digit after decimal, TOTAL – 8. it can work on 24 digits after decimal., , DOUBLE(M,D), , Real numbers with more precision up to 53 place after decimal., , DECIMAL, , It is used to store exact numeric value that preserve exact precision for e.g., money data in accounting system., DECIMAL(P,D) means P no. of significant digits (1-65), D represent no. of digit, after decimal(0-30), for e.g DECIMAL(6,2) means 4 digit before decimal and 2, digit after decimal. Max will be 9999.99, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 8 :
for more updates visit: www.python4csip.com, , Date and Time Types, Data type, , Description, , DATE, , A date in YYY-MM-DD format between 1000-01-01 to 9999-12-31, In oracle data format is DD-MON-YYYY for e.g 10-SEP-2019, , DATETIME, , Combination of date and time. For example to store 4th December, 2018 and time is afternoon 3:30 then it should be written as –, 2018-12-04 15:30:00, , TIMESTAMP, , Similar to DATATIME but it is written without hyphen for example the, above date time is stored as 20181204153000, , TIME, , To store time in the format HH:MM:SS, , YEAR(M), , To store only year part of data where M may be 2 or 4 i.e. year in 2, digit like 18 or 4 digit like 2018, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 9 :
for more updates visit: www.python4csip.com, , String Types, Data type, , Description, , CHAR(M), , Fixed length string between 1 and 255. it always occupy M size for each data, for example if size is CHAR(20) and we store value ‘MOBILE’ , although the size, of MOBILE is 6 but in a table it will occupy 20 size with space padded at right, side for remaining place., Mostly use in the case where the data to be insert is of fixed size like Grade, (A,B,C,..) or Employee code as E001, E002, etc. In this case CHAR will give, better performance than varchar, , VARCHAR(M), , Variable length string between 1 and 65535 (from MySQL 5.0.3) , earlier it was, 255. it takes size as per the data entered for example with VARCHAR(20) if the, data entered is MOBILE then it will take only 6 byte. It is useful for the data like, name, address where the number of character to be enter is not fixed., , VARCHAR2, , It is supported in ORACLE, both are almost same with minor difference. The, difference is in the way they are handling Empty String and NULL, for VARCHAR, these two are different where as VARCHAR2 treats both same., , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 10 :
for more updates visit: www.python4csip.com, , Difference between CHAR & VARCHAR, CHAR, , VARCHAR, , Fixed length string, , Variable length string, , Used where number of character to enter, is fixed like Grade, EmpCode, etc, , Used where number of character to be, enter is not fixed like name, address etc., , Fast, no memory allocation every time, , Slow, as it take size according to data so, every time memory allocation is done, , It takes more memory, , It takes less space, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 11 :
for more updates visit: www.python4csip.com, , NULL VALUE, • NULL means missing information, • NULL can appear in any type of column if it is not restricted by, NOT NULL or PRIMARY KEY, • Always remember NULL is neither equal to 0 nor space. NULL, means nothing, • Used in situation like if email id is not available with students, then we will insert NULL, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 13 :
for more updates visit: www.python4csip.com, , SQL COMMAND SYNTAX, Commands, , Description, , Keywords, , That have special meaning in SQL. They are the commands in mysql, , Clause, , They are used to support mysql commands like FROM, WHERE etc., , Arguments, , Values passed to clause like table name to FROM clause conditions to, WHERE clause for e.g., SELECT * FROM EMP WHERE SALARY>12000;, In the above command, SELECT is keyword, FROM AND WHERE is clause, EMP is an argument to FROM, SALARY>12000 is argument to WHERE, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 22 :
for more updates visit: www.python4csip.com, , PERFORMING SIMPLE CALCULATION, While performing SQL operations sometimes simple, calculations are required, SQL provides facility to perform, simple arithmetic operations in query. In MySQL we can, give these queries without FROM clause i.e. table name, is not required for these queries,, For Example, Select 10*2;, Select 10*3/6;, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 24 :
for more updates visit: www.python4csip.com, , COLUMN ALIAS, It is a temporary name/label given to column that will appear in, output. For example if column name is dept and you want, Department to appear as column heading then we have to give, Column Alias. If we want alias name of multiple words then it, should be enclosed in double quotes. Its format is :, ColumnName [AS] ColumnAlias, Example, (i) Select empno Employee_Number, name, dept Department,, Salary Income from emp;, (ii) Select name, Salary*12 as “Annual Income” from emp;, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 28 :
for more updates visit: www.python4csip.com, , WHERE clause, AND(&&) means both conditions must be true, OR(||), means any condition must be true to produce output., NOT(!) will do the reverse checking., , Select * from emp where salary>4000 and salary<8000;, Select * from emp where dept=‘Sales’ and salary<30000;, Select name,dept from emp where dept=‘HR’ and, salary>=20000 and salary<=40000;, Select * from emp where dept=‘HR’ or dept=‘IT’;, Select * from emp where NOT empno=4;, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 32 :
for more updates visit: www.python4csip.com, , LIKE, LIKE allows to search based on pattern. It is used when, we don’t want to search an exact value or we don’t know, that exact value, and we know only the pattern of value, like name starting from any particular letter, or ending, with and containing any particular letter or word., LIKE is used with two wildcard characters:, a) % : used when we want to substitute multiple, characters. With % length is not fixed, b) _ (underscore) : used when we want to substitute, Single character, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 36 :
for more updates visit: www.python4csip.com, , SORTING OUTPUT, By default records will come in the output in the same, order in which it was entered. To see the output rows in, sorted or arranged in ascending or descending order SQL, provide ORDER BY clause. By default output will be, ascending order(ASC) to see output in descending order, we use DESC clause with ORDER BY., Select * from emp order by name; (ascending order), Select * from emp order by salary desc;, Select * from emp order by dept asc, salary desc;, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 37 :
for more updates visit: www.python4csip.com, , MYSQL FUNCTIONS, A function is built – in code for specific purpose that, takes value and returns a single value. Values passed to, functions are known as arguments/parameters., There are various categories of function in MySQL:1) String Function, 2) Mathematical function, 3) Date and time function, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 39 :
for more updates visit: www.python4csip.com, , String Function, Function, , Description, , Example, , TRIM(), , Remove spaces from, beginning and ending, , Select TRIM(‘ Apple ‘); Output-’Apple’, , Select * from emp where trim(name) = ‘Suyash’;, INSTR(), , It search one string in, another string and, returns position, if not, found 0, , Select INSTR(‘COMPUTER’,’PUT’); Output-4, , LENGTH(), , Returns number of, character in string, , Select length(‘python’); Output- 7, Select name, length(name) from emp, , LEFT(S,N), , Return N characters of, S from beginning, , Select LEFT(‘KV OEF’,2); Output- KV, , RIGHT(S,N), , Return N characters of, S from ending, , Select RIGHT(‘KV OEF’,3); Output- OEF, , Select INSTR(‘PYTHON’,’C++’); Output – 0, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 40 :
for more updates visit: www.python4csip.com, , Numeric Function, Function, , Description, , Example, , MOD(M,N), , Return remainder M/N, , Select MOD(11,5); Output- 1, , POWER(B,P), , Return B to power P, , Select POWER(2,5); Output-32, , ROUND(N,D) Return number rounded to D, place after decimal, , Select ROUND(11.589,2); Output- 11.59, Select ROUND(12.999,2); Output- 13.00, , SIGN(N), , Return -1 for –ve number 1 for, +ve number, , Select sign(-10); Output : -1, Select sign(10); Output : 1, , SQRT(N), , Returns square root of N, , Select SQRT(144); Output: 12, , TRUNCATE(, M,N), , Return number upto N place, after decimal without rounding, it, , Select Truncate(15.789,2); Output: 15.79, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 41 :
for more updates visit: www.python4csip.com, , Date and Time Function, Function, , Description, , Example, , CURDATE()/, Return the current date, CURRENT_DATE(), / CURRENT_DATE, , Select curdate();, Select current_date();, , DATE(), , Return date part from datetime expression, , Select date(‘2018-08-15 12:30’);, Output: 2018-08-15, , MONTH(), , Return month from date, , Select month(‘2018-08-15’); Output: 08, , YEAR(), , Return year from date, , Select year(‘2018-08-15’); Output: 2018, , DAYNAME(), , Return weekday name, , Select dayname(‘2018-12-04’);, Output: Tuesday, , DAYOFMONTH(), , Return value from 1-31, , Select dayofmonth(‘2018-08-15’), Output: 15, , DAYOFWEEK(), , Return weekday index, for, Sunday-1, Monday-2, .., , Select dayofweek(‘2018-12-04’);, Output: 3, , DAYOFYEAR(), , Return value from 1-366, , Select dayofyear(‘2018-02-10’), Output: 41, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 42 :
for more updates visit: www.python4csip.com, , Date and Time Function, Function, , Description, , Example, , NOW(), , Return both current date, and time at which the, function executes, , Select now();, , SYSDATE(), , Return both current date, and time, , Select sysdate(), , Difference Between NOW() and SYSDATE() :, NOW() function return the date and time at which function was executed, even if we execute multiple NOW() function with select. whereas SYSDATE(), will always return date and time at which each SYDATE() function started, execution. For example., mysql> Select now(), sleep(2), now();, Output: 2018-12-04 10:26:20, 0, 2018-12-04 10:26:20, mysql> Select sysdate(), sleep(2), sysdate();, Output: 2018-12-04 10:27:08, 0, 2018-12-04 10:27:10, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 51 :
for more updates visit: www.python4csip.com, , GROUP BY, GROUP BY clause is used to divide the table into logical groups and, we can perform aggregate functions in those groups. In this case, aggregate function will return output for each group. For example, if we want sum of salary of each department we have to divide, table records, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 52 :
for more updates visit: www.python4csip.com, , Aggregate functions by default takes the entire table as a single group, that’s why we are getting the sum(), avg(), etc output for the entire table., Now suppose organization wants the sum() of all the job separately, or, wants to find the average salary of every job. In this case we have to, logically divide our table into groups based on job, so that every group will, be passed to aggregate function for calculation and aggregate function will, return the result for every group., , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 53 :
for more updates visit: www.python4csip.com, , Group by clause helps up to divide the table into logical groups based on any, column value. In those logically divided records we can apply aggregate, functions., For. E.g., , SELECT SUM(SAL) FROM EMP GROUP BY DEPT;, SELECT JOB,SUM(SAL) FROM EMP GROUP BY DEPT;, SELECT JOB,SUM(SAL),AVG(SAL),MAX(SAL),COUNT(*), EMPLOYEE_COUNT FROM EMP;, NOTE :- when we are using GROUP BY we can use only aggregate function and, the column on which we are grouping in the SELECT list because they will form a, group other than any column will gives you an error because they will be not, the part of the group., For e.g., , SELECT ENAME,JOB,SUM(SAL) FROM EMP GROUP BY JOB;, Error -> because Ename is not a group expression, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 54 :
for more updates visit: www.python4csip.com, , HAVING with GROUP BY, • If we want to filter or restrict some rows from the output produced by, GROUP BY then we use HAVING clause. It is used to put condition of group of, rows. With having clause we can use aggregate functions also., • WHERE is used before the GROUP BY. With WHERE we cannot use aggregate, function., • E.g., , • SELECT DEPT,AVG(SAL) FROM EMP GROUP BY DEPT HAVING, JOB IN (‘HR’,’SALES’), • SELECT DEPT,MAX(SAL),MIN(SAL),COUNT(*) FROM EMP, GROUP BY DEPT HAVING COUNT(*)>2, • SELECT DEPT,MAX(SAL),MIN(SAL) FROM EMP WHERE, SAL>=2000 GROUP BY DEPT HAVING DEPT IN(‘IT’,’HR’), VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 55 :
for more updates visit: www.python4csip.com, , JUST A MINUTE…, • Create the following table and add the records, ItemNo, , Item, , Dcode, , Qty, , UnitPrice StockDate, , 5005, , Ball Pen 0.5, , 102, , 100, , 16, , 2018-03-10, , 5003, , Ball Pen 0.25, , 102, , 150, , 20, , 2017-05-17, , 5002, , Gel Pen Premium, , 101, , 125, , 14, , 2018-04-20, , 5006, , Gel Pen Classic, , 101, , 200, , 22, , 2018-10-08, , 5001, , Eraser Small, , 102, , 210, , 5, , 2018-03-11, , 5004, , Eraser Big, , 102, , 60, , 10, , 2017-11-18, , 5009, , Sharpener Classic, , NULL, , 160, , 8, , 2017-06-12, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR

Page 56 :
for more updates visit: www.python4csip.com, , JUST A MINUTE…, Write down the following queries based on the given table:, 1) Select all record of table, 2) Select ItemNo, name and Unitprice, 3) Select all item record where Unitprice is more than 20, 4) Select Item name of those items which are quantity, between 100-200, 5) Select all record of Items which contains pen word in it, 6) Select unique dcode of all items, 7) Display all record in the descending order of UnitPrice, 8) Display all items which are stocked in the month of March, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR & SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR