Design at least 10 SQL queries for suitable database application using SQL DML statements: Insert, Select, Update, Delete Clauses using distinct, count, aggregation on Client-Data sever(three tier)
DATABASE : library TABLES : admin, users, books admin TABLE : desc : +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | uname | varchar(10) | NO | PRI | | | | passwd | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ select : +-------+--------+-------+ | uname | passwd | name | +-------+--------+-------+ | root | root | ADMIN | +-------+--------+-------+ users TABLE : desc : +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | uname | varchar(10) | NO | PRI | | | | passwd | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ select : +--------+--------+------------------+ | uname | passwd | name | +--------+--------+------------------+ | TEA101 | 101 | SONU KUMAR | | TEA102 | 102 | PIYUSH NIKAM | | TEA103 | 103 | VIRAL P | | TEA104 | 104 | MAYANK MRINAL | | TEA105 | 105 | KAMLESH JAISWAL | | TEA106 | 106 | | | TEA107 | 107 | | | TEA108 | 108 | | | TEA109 | 109 | | | TEA110 | 110 | | | TEA111 | 111 | | | TEA112 | 112 | TUSHAR SAINDANE | | TEA113 | 113 | PRADIP SHINDE | | TEA114 | 114 | ANKIT PAGAR | | TEA115 | 115 | | | TEA116 | 116 | | | TEA117 | 117 | | | TEA118 | 118 | | | TEA119 | 119 | | | TEA120 | 120 | | | TEA121 | 121 | DEEPAK PAGARO | | TEA122 | 122 | PRITAM DALVI | | TEA123 | 123 | ABHISHEK MANE | | TEA124 | 124 | SANDESH THOSAR | | TEA125 | 125 | SAHIL GAMBHIR | | TEA126 | 126 | DEEPTI W | | TEA127 | 127 | VAIBHAVI J | | TEA128 | 128 | AISHWARYA C | | TEA129 | 129 | | | TEA130 | 130 | | | TEA131 | 131 | KUBER VISHWAS | | TEA132 | 132 | SUMIT PINJARKAR | | TEA133 | 133 | | | TEA134 | 134 | SUMEDH WAGHMARE | | TEA135 | 135 | VINAYAK SONTAKKE | | TEA136 | 136 | PALLAVI SHINDE | | TEA137 | 137 | SHITAL LONDHE | | TEA138 | 138 | RAJASHRI A | | TEA139 | 139 | PAYAL R | | TEA140 | 140 | NAMRATA WABALE | | TEA141 | 141 | SNEHAL ZENDE | | TEA142 | 142 | | | TEA143 | 143 | | | TEA144 | 144 | GAYATRI HUNGUND | | TEA145 | 145 | ADITI T | | TEA146 | 146 | APARNA R | | TEA147 | 147 | RADHIKA IYYAR | | TEA148 | 148 | RINAZ SHAIKH | | TEA149 | 149 | PRACHI DALVI | | TEA150 | 150 | VARSHA MANDAVE | | TEA151 | 151 | OMKAR YESHVEKAR | | TEA152 | 152 | SWAPNIL RAYKAR | | TEA153 | 153 | | | TEA154 | 154 | | | TEA155 | 155 | AKASH KORADE | | TEA156 | 156 | | | TEA157 | 157 | | | TEA158 | 158 | | | TEA159 | 159 | | | TEA160 | 160 | | +--------+--------+------------------+ books TABLE : desc : +------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+----------------+ | book_id | int(11) | NO | PRI | NULL | auto_increment | | book_title | varchar(50) | YES | | NULL | | | book_author | varchar(20) | YES | | NULL | | | book_publication | varchar(20) | YES | | NULL | | +------------------+-------------+------+-----+---------+----------------+ select : +---------+----------------------------------------------------+----------------------+----------------------+ | book_id | book_title | book_author | book_publication | +---------+----------------------------------------------------+----------------------+----------------------+ | 1 | Let us C | Yeshavant Kanetkar | BPB Publication | | 2 | Let us C++ | Yeshavant Kanetkar | BPB Publication | | 3 | C in Depth | S K Shrivastav | Tata Mc-GrawHill | | 4 | Data Structures Through C | Yeshavant Kanetkar | BPB Publication | | 5 | Introduction to Automata Theory Languages And Comp | John E. Hopcoroft | LPE | | 6 | Theory Of Computer Science | K.L.P. Mishra | ABC | | 7 | Introduction to The Theory of Computation | Michael Sipser | ISE | | 8 | Theory of Computation | Vivek Kulkarni | Oxford University Pr | | 9 | An Introduction to Operating Systems | P. Bhatt | PHI | | 10 | Unix and Linux System Administration Handbook | Evi Nemeth | ISBN | | 11 | Operating Systems: A Concept Based Approach | Dhanajay M. Dhamdher | McGrawHill Education | | 12 | Computer Networking a Top Down Approach Featuring | Kurose, Ross | Pearson | | 13 | Wireless Sensor Networks | Fang Zhaho | Elsevier | | 14 | Embedded Systems: An Integrated Approach | Lyla B. Das | Pearson | | 15 | Wireless Sensor Networks | Robert Faludi | OREILLY | | 16 | Database System concepts | Abraham Silberschatz | McGraw Hill Internat | | 17 | Big Data Analytics with R and Hadoop | Vignesh Prajapati | Pearson | | 18 | Data mining | Margaret H. Dunham | Pearson publication | | 19 | Hadoop in action | Chuck Lam | McGrawHill Education | | 20 | Digital Evidence& Computer Crime | Eoghan Casey | ELSEVIER-Academic Pr | | 21 | Modern Embedded Computing | Peter Barry | Elsevier | | 22 | Data Communications and Networking | Fourauzan B. | Tata McGraw- Hill Pu | | 23 | Optical Networks a Practical Perspective | Rajiv Ramaswami, Kum | Elsevier | | 24 | Software Engineering : A Practitioner’s Approach | Roger S Pressman | Mcgraw-Hill | | 25 | An Integrated Approach to Software Engineering | Pankaj Jalote | Narosa Publication | | 26 | Discrete Mathematics | N. Biggs | Oxford University Pr | | 27 | Discrete Mathematics and its Applications | Kenneth H. Rosen | McGraw-Hill | | 28 | Modern Digital Electronics | R. Jain | Tata McGraw-Hill | | 29 | Digital Principles | Flyod | Pearson Education | | 30 | The Design of the Unix Operating System | Maurice J. Bach | Pearson Education | | 31 | Data Structures: A pseudo code approach with C | R. Gilberg, B. Forou | Cengage Learning | | 32 | Microprocessors & Interfacing | Douglas Hall | McGraw Hill | | 33 | The 8086/88 Family: Design, Programming & Interfac | John Uffenbeck | PHI | | 34 | TSR through C | Yeshvant kanetkar | BPB Publication | | 35 | Computer Graphics | S. Harrington | McGraw-Hill Publicat | | 36 | Computer Architecture and Organization | Jhon P Hays | McGraw-Hill Publicat | | 37 | Computer Organization | Zaky S. Hamacher | McGraw-Hill Publicat | +---------+----------------------------------------------------+----------------------+----------------------+ "*****************************************************************************************************************************************" //////////////////////////////////////////////////////// MYSQL QUERIES //////////////////////////////////////////////////////// "*****************************************************************************************************************************************" "*************************************************************** DATABASE ***************************************************************" * show databases; * create database library; * use library * drop database library; "*************************************************************** TABLES ***************************************************************" * show tables; * create table books (book_id int, book_title varchar(50), book_author varchar(20), book_publication varchar(20), primary key(book_id)); * drop table books; "****** NOTE ******" "CONSTRAINTS : NOT NULL, AUTO_INCREMENT, UNIQUE, PRIMARY KEY" "************************************************************ INSERT QUERY ***************************************************************" insert into books(book_id, book_title, book_author, book_publication) values (1, 'qwe', 'asd', 'zxc'); ORRRRRRRR insert into books values (1, 'qwe', 'asd', 'zxc'), (2, 'wer', 'sdf', 'xvc'), (3, 'ert', 'dfg', 'cvb'); "************************************************************ SELECT QUERY ***************************************************************" SYNTAX ==>>> "SELECT field1, field2,...fieldN from table_name1, table_name2... [WHERE Clause] [OFFSET M ][LIMIT N]" 1) You can use one or more tables separated by comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of SELECT command. 2) You can fetch one or more fields in a single SELECT command. 3) You can specify star (*) in place of fields. In this case, SELECT will return all the fields. 4) You can specify any condition using WHERE clause. 5) You can specify an offset using OFFSET from where SELECT will start returning records. By default, offset is zero. 6) You can limit the number of returns using LIMIT attribute. * select * from books; * select book_id, book_title from books; * select * from books where book_id = 1; * select * from books where book_title IS NULL; // select * from books where book_title = NULL; * select * from books where book_title IS NOT NULL; // select * from books where book_title != NULL; * select * from books, users; * select * from books limit 5; "******************************************************** DELETE QUERY *************************************************************" SYNTAX ==>>> " DELETE FROM table_name [WHERE Clause] " * delete from books where book_id = 23; * delete from books where book_id = 23 or book_name= 'Let us C'; +---------------------------------------------------------------------------------------------------------------------------------------+ | " ALTER QUERY " | +---------------------------------------------------------------------------------------------------------------------------------------+ | 1) drop column: | | | | *) ALTER TABLE books DROP book_publication; | | | | 2) add column: | | | | *) ALTER TABLE BOOKS ADD book_publication varchar(20); | | | | 3) add column at some position: | | | | *) ALTER TABLE BOOKS ADD book_publication varchar(20) first; // default last possition | | | | *) ALTER TABLE BOOKS ADD book_publication varchar(20) after book_id; | | "To change a column's definition, use MODIFY or CHANGE clause along with ALTER command " | | | 4) change column book_title from CHAR(1) to CHAR(10) *) ALTER TABLE books MODIFY book_title CHAR(10); | 5) After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name *) ALTER TABLE testalter_tbl CHANGE i j BIGINT; *) ALTER TABLE testalter_tbl CHANGE i i BIGINT; // without changing name 6) modify with new contraints: *) ALTER TABLE book_id MODIFY coulmn1 BIGINT NOT NULL DEFAULT 100; 7) Changing a Column's Default Value: *) ALTER TABLE tbl ALTER column1 SET DEFAULT 1000; 8) remove default constraint from any column by using DROP clause along with ALTER command: *) ALTER TABLE tbl ALTER column1 DROP DEFAULT; 9) Renaming TABLE: *) ALTER TABLE testalter_tbl RENAME TO alter_tbl; +---------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------+ "************************************************************ CLAUSES *******************************************************************" ####################################### WHERE CLAUSE ######################################################### SYNTAX ==>>> "SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2....." * select * from users where uname='TEA112' and name = 'TUSHAR SAINDANE' or passwd = '124'; * select * from books where book_id > 13; "********** NOTE ********* " can use =, !=, >, <, >=, <= " ####################################### LIKE CLAUSE ######################################################### 1) A WHERE clause with equals sign (=) works fine where we want to do an exact match. 2) But there may be a requirement where we want to filter out all the results where book_author name should contain "yesh". 3) LIKE clause is used along with % characters, then it will work like a meta character (*) in UNIX while listing out all the files or directories at command prompt. 4) When LIKE is used along with % sign, then it will work like a meta character search. 5) You can specify more than one conditions using AND or OR operators. SYNTAX ==>>> "SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'" * select * from books where book_author like '%yesh'; * select * from books where book_author like 'yesh%'; * select book_id, book_title from books where book_author like '%yesh%'; +---------+---------------------------+ | book_id | book_title | +---------+---------------------------+ | 1 | Let us C | | 2 | Let us C++ | | 4 | Data Structures Through C | | 34 | TSR through C | +---------+---------------------------+ ####################################### ORDER BY CLAUSE ######################################################### *********************** SORTING PURPOSE *********************** SYNTAX==>>> "SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]" * SELECT * from books ORDER BY book_id ASC * SELECT * from books ORDER BY book_id DESC ####################################### GROUP BY CLAUSE ######################################################### "You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. "You can use COUNT, SUM, AVG, etc., functions on the grouped column." +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ |1 | John | 2007-01-24 | 250 | |2 | Ram | 2007-05-27 | 220 | |3 | Jack | 2007-05-06 | 170 | |3 | Jack | 2007-04-06 | 100 | |4 | Jill | 2007-04-06 | 20 | |5 | Zara | 2007-06-06 | 300 | |5 | Zara | 2007-02-06 | 350 | +------+------+------------+--------------------+ *) SELECT COUNT(*) FROM employee_tbl; *) SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +------+----------+ | name | COUNT(*) | +------+----------+ | Jack | 2 | | Jill | 1 | | John | 1 | | Ram | 1 | | Zara | 2 | +------+----------+ STUDY OTHER IMPORTANT FUNCTIONS AND CLAUSES FROM PG NO. 92 OF mysql_tutorial.pdf $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ "SQL JOINS" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 1) we have only been getting data from one table at a time. This is fine for simple takes, but in most real world MySQL usage, you will often need to get data from multiple tables in a single query. 2) example we have 2 tables: admin & users * select * from admin; +-------+--------+-------+ | uname | passwd | name | +-------+--------+-------+ | root | root | ADMIN | +-------+--------+-------+ * select * from users; +--------+--------+------------------+ | uname | passwd | name | +--------+--------+------------------+ | TEA101 | 101 | SONU KUMAR | | TEA102 | 102 | PIYUSH NIKAM | | TEA103 | 103 | VIRAL P | | TEA104 | 104 | MAYANK MRINAL | | TEA105 | 105 | KAMLESH JAISWAL | | TEA106 | 106 | | | TEA107 | 107 | | | TEA108 | 108 | | | TEA109 | 109 | | | TEA110 | 110 | | | TEA111 | 111 | | | TEA112 | 112 | TUSHAR SAINDANE | | TEA113 | 113 | PRADIP SHINDE | | TEA114 | 114 | ANKIT PAGAR | | TEA115 | 115 | | | TEA116 | 116 | | | TEA117 | 117 | | | TEA118 | 118 | | | TEA119 | 119 | | | TEA120 | 120 | | | TEA121 | 121 | DEEPAK PAGARO | | TEA122 | 122 | PRITAM DALVI | | TEA123 | abhi | abhi | | TEA124 | 124 | SANDESH THOSAR | | TEA125 | 125 | SAHIL GAMBHIR | +--------+--------+------------------+ JOINING ::::::::: *) select a.uname, a.passwd, b.uname, b.passdw from admin a, users b; *) select a.uname, a.passwd, b.uname, b.passwd from admin a, users b where b.uname = 'TEA123'; +-------+--------+--------+--------+ | uname | passwd | uname | passwd | +-------+--------+--------+--------+ | root | root | TEA123 | abhi | +-------+--------+--------+--------+ ..........................pending................................ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ "REGULAR EXPRESSION" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 1) regular expression............ ^ ===>>> Beginning of string $ ===>>> End of string . ===>>> Any single character [...] ===>>> Any character listed between the square brackets [^...] ===>>> Any character not listed between the square brackets p1|p2|p3 ===>>> Alternation; matches any of the patterns p1, p2, or p3 * ===>>> Zero or more instances of preceding element + ===>>> One or more instances of preceding element {n} ===>>> n instances of preceding element {m,n} ===>>> m through n instances of preceding element EXAMPLES: 1) Query to find all the names starting with 'st' * SELECT name FROM personal_info WHERE name REGEXP '^st'; 2) Query to find all the names ending with 'ok' * SELECT name FROM person_tbl WHERE name REGEXP 'ok$'; 3) Query to find all the names, which contain 'mar' * SELECT name FROM person_tbl WHERE name REGEXP 'mar'; 4) Query to find all the names starting with a vowel and ending with 'ok' * SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$'; ******************************************************************************************************************************************* AGGREGATION ******************************************************************************************************************************************* Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a single value. SQL offers five built-in aggregate functions: Average: avg Minimum: min Maximum: max Total: sum Count: count select avg (salary) from instructor where dept name= ’Comp. Sci.’; select count (distinct ID ) from teaches where semester = ’Spring’ and year = 2010; "AGGREGATION WITH GROUP BY CLAUSE": select book_publication, count(book_id) from books group by book_publication; +----------------------+----------------+ | book_publication | count(book_id) | +----------------------+----------------+ | ABC | 1 | | BPB Publication | 4 | | Cengage Learning | 1 | | Elsevier | 3 | | ELSEVIER-Academic Pr | 1 | | ISBN | 1 | | ISE | 1 | | LPE | 1 | | McGraw Hill | 1 | | McGraw Hill Internat | 1 | | Mcgraw-Hill | 2 | | McGraw-Hill Publicat | 3 | | McGrawHill Education | 2 | | Narosa Publication | 1 | | OREILLY | 1 | | Oxford University Pr | 2 | | Pearson | 3 | | Pearson Education | 2 | | Pearson publication | 1 | | PHI | 2 | | Tata Mc-GrawHill | 1 | | Tata McGraw- Hill Pu | 1 | | Tata McGraw-Hill | 1 | +----------------------+----------------+ 23 rows in set (0.02 sec)
Tags:
PROGRAMMING