N.G.Acharya & D.K.Marathe college Chembur-E, Mumbai-71

1. K.P.B. HINDUJA COLLEGE OF COMMERCEWorkshop in Computer Programming: S.Y.B.Com. Computer Systems and Applications: T.Y.B.Com. MySQL : Prof. Pradeep Kore N.G.Acharya & D.K.Marathe college Chembur-E, Mumbai-71

2. MySQL • Set of My-SQL functions was decided in the previous workshop(s) should continue ! • String functions:- • 1. CONCAT(str1,str2..) • 2. LOWER(str.) , UPPER(str.) • 3. LENGTH(str.) • 4. LTRIM(str.) , RTRIM(str.) ,TRIM(str.) • 5. LEFT(str., len) , RIGHT(str., len) , MID(str, pos, len) and SUBSTRING()

3. MySQL • Date functions :- • 1. NOW() , TIME(expr) and CURDATE() • 2. DAY(date) , MONTH(date) and YEAR(date) • 3. DAYNAME(date) and MONTHNAME(date) • 4. DATE(expr) • Numeric functions :- • 1. ABS(x) 2. EXP() • 3. POW() 4. MOD() • 5. ROUND() 6. SQRT()

4. MySQL • Aggregate or Summary or Group functions:- • COUNT() : For counting number of the rows of the specified column. • SUM() : For summing of the non null values in the specified column. • AVG() : For averaging of the non null values in the specified column. • MIN() : For finding minimum of the non null values in the specified column. • MAX() :For finding maximum of the non null values in the specified column.

5. MySQL • In the previous years patterns:- • Marking scheme of all five units in annual exam. paper. • Five units of the syllabus (80 marks paper): (=16x5) • Five units of the same syllabus (annual pattern) 60 marks : ( =12x5) Now in the current semester based system of 2013-14 • Five units of Sem-V (60 marks)+ Sem-VI (60 marks) • Total 120 marks : (=24x5) with out options. • With 50 % options 180 marks : (=36x5)

6. MySQL • Marking scheme of MySQL in Question Paper:- • Q 2. A) Attempt any two sub-questions from a, b, c in MySQL • a) MySQL (5) • b) MySQL (5) • c) MySQL (5) • Q 3. A) Attempt any two sub-questions from a, b, c in MySQL • a) MySQL (5) • b) MySQL (5) • c) MySQL (5) • Q 4. • B) Attempt any four sub-questions from d, e, f, g, h, i in MySQL (Multiple Choice) (4) • d) MySQL • e) MySQL • f) MySQL • g) MySQL • h) MySQL • i) MySQL • Total marks for MySQL is 10+10+4 =24 and with options 15+15+6=36

7. MySQL • Five marks six questions can be • 1. Create table question :- (This question must be carefully designed so that no variation in marking schemes at the paper assessment. ) • 2.Simple queries, sorting queries, queries with condition(s), queries with calculating results with new heading(s) using existing columns. • 3. Group/ Summary Queries using Group by clause(& using group functions : COUNT(), SUM(), AVG(), MAX() & MIN() • 4. Multi table queries: Simple joins (INNER JOIN) only • 5. Nested queries ( Only up to two levels) : Subqueries.

8. MySQL • 6. i) Changing table structure ( Alter table…add/drop/change/ modify/rename …etc ) • ii) Adding / deleting /updating rows/ row contains in the existing table. • iii) Renaming /Removing databases, tables. Displaying list of existing databases, tables in a existing database. iv) Simple transaction:- START, COMMIT & ROLLBACK. v) Queries using MySQL functions.

9. MySQL • Discussion with reference to MySQL :- • To define exact set of MySQL functions. • Queries using limit clause can be included as scope of syllabus. • To define exact marking scheme of multi-table queries. (Since in some question papers one query 5 marks or in some papers its carry 2 marks.) • In multi-table queries Self Join discuss or not?