Informatic Practile Grade 11 Cbse .

A text Book on information science PRACTICES CLASS XI Shiksha Kendra, 2, conjunction Centre, Preet Vihar, Delhi-110 092 India A text book on In actatics Practices, Class XI. PRICE Rs. graduation EDITION 2010 CBSE, India COPIES This book or part in that respectof whitethorn non be reproduced by each person or agency in every manner. PUBLISHED BY The Secretary, Central Board of Secondary Education, Shiksha Kendra, 2, Community Centre, Preet Vihar, Delhi-110092 DESIGN, LAYOUT Multi Graphics, 5745/81, Reghar Pura, Karol Bagh, New Delhi-110005, Ph unity 25783846 PRINTED BY ii ForewordInformation and conversation Technology has permeated in every walk of life affecting the technology fields such as launching satellites, managing businesses across the globe and in some(prenominal) case enabling social ne cardinalrking. The convergence of computer, communication and content technologies, being k without delay as ICT, have attracted attention of academia, business, government and communities to engagement it for innovative useable propositions. Year by year it is becoming transparentr to use devices such as desktop, palm top, iPod, etc. 21st century is characterized with the emergence of knowledge ground society wherein ICT plays a pivotal role.In its vision, the National Policy on ICT in indoctrinate Education by MHRD, Govt. of India, states The ICT policy in tutor Education aims at preparing y prohibitedh to participate creatively in the establishment, reenforcement and g lineth of a knowledge society leading to every almost socio economic development of the nation and global competitiveness. The policy envisages triplet stages of ICT implementations at school level ICT literacy and Competency Enhancement, ICT en fittingd t each(prenominal)inglearning, and introduction of ICT related elective course subjects at Senior Secondary level.With this light uponst wizdrop a major ikon shift is imperative in imparting ICT-enabled instructions, collaborative learning, multidisciplinary problem-solving and promoting critical thinking skills as envisaged in the National curriculum framework 2005. Foundation of these skills is invest at school level. Armed with such skills it is expected that a schoolchild entirelyow for transform knowledge into easy to use systems to the ultimate put on of the society at large.Syllabus of Informatics Practices has been revisited accordingly with a instruction on generic concepts with domain specific practical experiments and projects to ensure abstract knowledge with practical skills. The societal impact of ICT have been discussed. A freshly Unit on IT Applications has been added to enhance extrapolateing of the above tools and techniques to solve documentary life problems by designing twain front end and back end with proper mete outive information connectivity. Introduction of Open streamers and Open semen to promote Vendor Neutrality of tools.Creativity and Collaborative Learning/ computer programing is in any case an added feature. Specific on the wholey, a Unit on Ne bothrking and Open Standards is introduced in locate of Business Computing. For IDE ground programming, Java is introduced in place of VB. relative Database counselling System is dealt with utilize My SQL in place of SQL & PL/SQL development Oracle. The CBSE had been recomm death different books in the past. With a total overhauling of the course on Informatics Practices it has ventured to bring unwrap a comprehensive text book for tout ensemble(prenominal) units for the first beat.I am happy to discontinue Part-1 of Informatics Practices for Class XI. I would c atomic telephone number 18 to express my deep appreciation to the text book development police squad for their contri thoion and to the convener of the team, Prof. Om Vikas who aptly steered this activity. Appreciation is alike due to Mrs. C Gurumurthy, managing director (Academic) and Dr. (Smt) Srijata Da s, Education Officer, for planning, coordinating and executing this initiative and bringing out this publication. It is hoped that all savants and instructors give benefit by making best use of this publication.Their feedback will be highly appreciated for further improvement. VINEET JOSHI CHAIRMAN H l d d H d d x , H d H l lt l h d T d x l x d d l L d l td d d t p D l H l d h L l d h l d , d, l D d d h x d h , d l p d h 100 ASkills100 GK100) JOptionPane. showMessageDialog(this,Re- encrypt label (Out of 100)) v if the attach input by the user for any of the subjects argon greater than 100 Check or non an if they ar hence vaunt the message Re-Enter attach (Out of 100). Since we have to presentment the hallucination message if the attach of even unmatchable subject be out of limit so we have apply the street girl which means OR.So in simple english it means if tag of English are 100 or marks of ASkills 100 or marks of GK 100, therefore boast the flaw message. So the message will be discloseed even if entirely atomic number 53 break quantifys to true. if (ASkills=90 && GK=90 ) JOptionPane. showMessageDialog(this,** Selected for Achievers divide **) jtext guinea pig6. practiceText(*) 158 informatics PRACTICES Chapter-6 keep back Structures v if the marks of ASkills and GK are both = 90 or non. If they are therefore Check pomposity the message ** Selected for Achievers Award ** and similarly display a * in the text field.Since we have to check that both the marks should be greater than 90 so we have use the && operator which in simple English means AND. So the condition will evaluate to true only if both the conditions are satisfied. Let us now make unnecessary the code for the Grade calculator lotion as shown in Figure 6. 38 private void jButton1ActionPerformed(java. awt. event. ActionEvent evt) // Variable Declaration and appointment trading operations int quantity,English,ASkills,GK English=Integer. parseInt(jText sk etch1. getText()) ASkills=Integer. parseInt(jTextField2. getText()) GK=Integer. arseInt(jTextField3. getText()) //Validation of Entered Marks if (English100 ASkills100 GK100) JOptionPane. showMessageDialog (this,Re-Enter Marks (Out of 100)) else Total=English+ASkills+GK jTextField4. punctuateText(Integer. toString(Total)) jButton2. personateEnabled(true) private void jButton2ActionPerformed(java. awt. event. ActionEvent evt) // Variable Declaration and assignment operations char Grade int ASkills,GK,Total ASkills=Integer. parseInt(jTextField2. getText()) GK=Integer. parseInt(jTextField3. getText()) Total=Integer. arseInt(jTextField1. getText()) information processing PRACTICES 159 Chapter-6 Control Structures //Decision for Achievers Award if (ASkills=90 && GK=90 ) JOptionPane. showMessageDialog (this,** Selected for Achievers Award **) jTextField6. specialiseText(*) //Finding Grade if (Total=80) jTextField5. preparednessText(A) else if (Total=70) jTextField5. watchText (B) else if (Total=60) jTextField5. alignText(C) else if (Total=50) jTextField5. vexText(D) else jTextField5. setText(E) private void jButton3ActionPerformed(java. awt. event.ActionEvent evt) // To Exit from application System. exit(0) Figure 6. 38 Code for the Grade Calculator Application Since in this application we had to test for multiple conditions in a if program line, so we had to matrimony the conditions employ nigh operators. Such conditions that are formed by joining simple conditions are called complex conditions and they are usually joined development the ar leand operators. Logical instrument A logical operator denotes a logical operation. Logical operators and sex actal operators are apply together to form a complex condition. Logical operators are 160information processing PRACTICES Chapter-6 Control Structures Operator && Use a10 && b10 bMySQL Command Line thickening OR Goto the folder CProgram FilesMySQLMySQL horde 5. 1in as the drive having MySQ L And Click on the file MySQL. EXE MySQL will impel a message to provide password (it requires the uniform password which was entered during the readiness) Enter Password**** Welcome to the MySQL monitor. Commands end with or g. Your MySQL connection id is 4 Server version (GPL) 5. 0. 51a-community-nt MySQL Community Edition Assuming C drive fictional character booster or h for help.Type c to clear the buffer. Mysql To exit from MySQL, type QUIT or EXIT MysqlQUIT The above steps ensure successful installation and configuration of MySQL selective informationbase server. Next time in the MySQL prompt, one after part take in and use entropybases, derive water dodges and execute SQL queries. Downloading MySQL Linux Environment Installation of the binary version of MySQL, release 4. 0. 20, to run on Linux is as follows Installation file for MySQL may be downloaded from the link information processing PRACTICES 203 Chapter-8 Introduction to MySQL http//dev. mysql. com/download s/mysql/5. 1. tmldownloads (Choose appropriate download link as per the desired operating system) Create MySQL User Account cd /usr/ topical anaesthetic groupadd mysql useradd -c MySQL Software Owner -g mysql mysql passwd mysql changing password for user mysql. password all authentication tokens up exitd successfully. Installing Binary Version Unzip the files and qualifying the directory to mysql cd mysql scripts/mysql_install_db user=mysql Preparing db shelve Preparing host add-in Preparing user plank Preparing func mesa The latest information about(predicate) MySQL is procurable on the net at http//www. ysql. com Support MySQL by buying support/licenses at https// allege. mysql. com 204 information science PRACTICES Chapter-8 Introduction to MySQL Start and Stop The Database Software Starting the MySQL Database su cd /usr/ topical anesthetic/mysql bin/mysqld_safe user=mysql & Starting mysqld daemon with databases from /usr/local/mysql/data fillet the MySQL Da tabase su cd /usr/local/mysql bin/mysqladmin -u root shutdown 040803 233627 mysqld ended 1+ Done Know much Visit the hobby website to find a vast list of free and open acknowledgment software products available http//en. wikipedia. rg/wiki/List_of_free_and_open_source_software_packages bin/mysqld_safe user=mysql Summary A database is an organised accretion of data. v Data vis stored in a relational database in one or more(prenominal)(prenominal) get acrosss. A group v of speechs and editorials forms a parry. The plain subset of a Table is know as a Row/Tuple. v The vertical subset of a Table is known as a tug/Attri bute. v A expectation key is an attribute (or a set of attributes) that uniquely identifies a v language. A patriarchal Key is one of the usher outdidate keys. Only v one of the Candidate keys is selected as the patriarchal key of a dishearten. All other layaboutdidate keys are called tack on keys.INFORMATICS PRACTICES 205 Chapter-8 Introduction to MySQL multiplex Choice Questions 1. A relation bum have only one ________ key and may have more than one _______ keys. a) b) c) d) 2. Primary, Candidate Candidate, Alternate Candidate, Primary Alternate, Candidate The vertical subset of a elude is known as a) b) c) d) Tuple Row Attribute Relation 3. If software is released under open source, it means a) b) c) d) It is expensive. Its source code is available to the user. It belongs to a company. It is a database management system. 4. Which of the future(a) editorials in a savant submit bottom of the inning be utilise as the primary key? ) b) c) d) Class share First ca-ca Admission No 5. A tuple is also known as a ___________________________ . a) b) c) d) table relation row field INFORMATICS PRACTICES 206 Chapter-8 Introduction to MySQL 6. An attribute is also known as a_________________________. a) b) c) d) table relation row mainstay 7. A field or a combination of fields in a table that has a unique mensurate for each row is called a) b) c) d) Candidate key. Foreign key. Main key. Alternate key. Exercises 1. resolution the following questions a) Define the following terms i) ii) Database Table iii) Primary key iv) v) b) c) d) e) f) 2. Candidate key Alternate keyWhat is the relationship betwixt a Database and a Table? What is database management system? Write marks of any two DBMSs. How is data organized in a table? What is a Primary key? What is its purpose in a table? What is MySQL? Distinguish between the following pairs a) b) Row and editorial Primary key and Candidate key. INFORMATICS PRACTICES 207 9 Learning Objectives after(prenominal) studying this lesson the students will be able to State vcategories of SQL averments. Create v a database Create v a table. Add rows to a table. v MySQL Retrieve v data in various ways from table using engage record. screening v data in a sorted way using order BY article.Modify v data stored in a table. aspect v body structure of a table Modify v structure of table Delete v rows from a table In the precedent lesson, you have learnt that Relational Databases use tables to store data. A table just refers to a two dimensional representation of data using pillars and rows. MySQL lets us manipulate and manage these tables in an efficient way. We have learnt that MySQL is a Relational Database Management System. In this lesson we will learn about SQL (Structured Query address). It is a Standard language used for accessing and manipulating relational databases. Ms.Sujata is a Class teacher of Class XI. She wants to store data of her students i. e. fleshs and marks secured, in a database. A database is used to house data in the form of tables. She uses a score DATABASE assertion to create a spick-and-span database maked School. 208 INFORMATICS PRACTICES Chapter-9 MySQL mysql realise DATABASE School one time the above mentioned disputation gets executed, a database with the secernate School is created on her system. No w she has to open the database to work on it. For this USE avowal is required. She opens the School database description entered by user mysql USE School Database ChangedDisplay by system Now, MySQL prompt laughingstock accept any head related to the database School. Semicolon is standard way to end SQL logical argument. Creating a table After creating a database, the next step is creation of tables in the database. For this fix hold over debate is used. Syntax CREATE board ( , , , ) Since Ms. Sujata is just learning, she initially creates a simple table numberd savant with only two tugboats RollNo and recognize in the School database. To do this, she enters the following argumentation mysql CREATE turn off scholarly person ( RollNo integer, fix VARCHAR(25) ) INFORMATICS PRACTICES 209 Chapter-9 MySQL v Give meaningful secernate to a table. If a table will store information about students, name it STUDENTS, not Abc or Person. v label and column name are not case sensitive. For example, Table STUDENTS is treated the same as STuDents or students. We will study about the CREATE carry over statement in spot later in this lesson. What if Ms. Sujata wants to get out the names of all the tables in the database? At any point of time, she can view names of all the tables contained in the current database by using SHOW submitS statement as shown down the stairs mysql SHOW TABLES ++ Tables_in_school ++ scholarly person + 1 row in set (0. 00 sec) Once the table named Learner is created, Ms. Sujata would like to add data of students in the table, which is also known as populating table with rows. To add row(s) in the table she uses the submit INTO statement Syntax participate INTO determine (,, ,) 210 INFORMATICS PRACTICES Chapter-9 MySQL She inserts 4 rows mysql enrol INTO Learner set (14,Aruna Asaf Ali) mysql set up INTO Learner VALUES (12,Tarun Sinha) mysql introduce INTO Learner VALUES (16,John Fedrick) mysql tuck INTO Learner VA LUES (10,Yogi Raj Desai) In acquaint statement Character, date and Time data should be cover in Quotes. numerical set should not be enclosed in quotes. Now that she has added 4 rows in the table, she wants to view the circumscribe of the table. How can she do that? To view the contents of the table, she uses the following recognize statement. In the simplest way, make statement is used like this Syntax demand * FROM So, she types the statement mysql fill * FROM Learner ++ RollNo Name ++ 14 12 16 10 Aruna Asaf Ali Tarun Sinha John Fedrick Yogi Raj Desai + In the above statement, FROM article states which table to look in for data. Any time to know the database presently in use, the get hold of DATABASE() statement can be used. INFORMATICS PRACTICES 211 Chapter-9 MySQL mysql need DATABASE() DATABASE() school 1 row in set (0. 0 sec) Statements in MySQL are not case sensitive. It means select DATABASE() or postulate DATABASE() or apportion database() would all work the same way. Some Terminologies Keyword A keyword refers to a special word that has a special meaning to SQL. For example, give and FROM are keywords.Clause A clause is a portion of an SQL statement. from each one clause is identified by a keyword. For example, consider the statement lead name FROM Learner present pick out name is a clause. hold is a statement as well as a clause. select clause is everything from keyword choose until keyword FROM. study statement is the entire command. FROM Learner is a FROM clause, which specifies the table from which data has to be selected. Statement A statement is a combination of two or more clauses. For example, SELECT name FROM Learner is a statement. 212 INFORMATICS PRACTICES Chapter-9 MySQLMySQL Data Types Well, in the first place we learn more about making a table, there is one thing we need to say first Data Types. They indicate the type of data that you are storing in a prone table column. So, what are the differ ent Data Types available in MySQL? Here is a list of some of the most popular ones and what type of determine they hold Class Text Data Type CHAR(size) Description A fixed-length string from 1 to 255 characters in length remediate-padded with spaces to the undertake length when stored. Values must(prenominal) be enclosed in individual(a)(a) quotes or double quotes. physical exercise Maths TexT VARCHAR(size)A variable-length string from 1 to 255 characters in length for example VARCHAR(25). Values must be enclosed in single quotes or double quotes. Computer Me and u Numeric DECIMAL(size,d) It can represent number with or without the fractional part. The maximum number of digits may be qualify in the size parameter. The maximum number of digits to the obligation of the decimal point is specified in the d parameter INT Or INTEGER It is used for storing integer jimmys. You can specify a comprehensiveness upto 11 digits. 17. 32 345 76 INFORMATICS PRACTICES 213 Chapter-9 MySQL Date DATE It represents the date including day, month and year It represents time.Format HHMMSS Note The supported rate is from -8385959 to 8385959 2009-0702 TIME() TIME Categories of SQL Commands SQL commands can be classified into the following categories 1. Data Definition Language (DDL) Commands The DDL part of SQL permits database tables to be created or canceld. It also defines indices (keys), specifies links between tables, and imposes constraints on tables. Examples of DDL commands in SQL are v CREATE DATABASE creates a current database v CREATE TABLE creates a saucy table v TABLE modifies a table castrate v TABLE strike downs a table empty 2. The Data Manipulation Language (DML) CommandsThe interrogative sentence and modify commands form the DML part of SQL Examples of DDL commands are v SELECT extracts data from a table v update updates data in a table v scratch deletes data from a table v tuck INTO inserts new-sprung(prenominal) data into a table CRE ATE TABLE Ms. Sujata feels good that she has successfully created a table named Learner with 2 columns using CREATE TABLE statement. She now creates a table named bookman with 214 INFORMATICS PRACTICES Chapter-9 MySQL four columns. When tables are created its columns are named, data types and sizes are supplied for each column. While creating a table at least one column must be specified.Syntax CREATE TABLE ( column name , ( column name , ) Example mysql USE school Database spayd mysql CREATE TABLE student( Rollno INTEGER, Name VARCHAR(25), Gender CHAR(1), Marks1 DECIMAL(4,1)) Query OK, 0 rows affected (0. 16 sec) If table scholarly person already exists in database school, then the error message Table bookman already exists is displayed. Each column in the table is precondition a unique name. In the example above the column names are Rollno, Name etc. This doesnt mean each column that is named has to be unique deep down the entire database.It only has to be unique within th e table where it exists. Also notice that the names do not use any spaces. When naming tables and columns be sure to keep it simple with earn and verse. Spaces and symbols are invalid characters except for underscore(_). Column names like first_name,last_name,email are valid column names. Viewing Structure of Table The DESCRIBE statement can be used to see the structure of a table as indicated in the Create Statement. It displays the Column names, their data types, whether Column must contain data ,whether the Column is a Primary key etc.INFORMATICS PRACTICES 215 Chapter-9 MySQL Syntax DESCRIBE OR DESC mysql DESCRIBE savant Statement entered by user ++++++-+ Field Type Null Key evasion Extra ++++++-+ Rollno int(11) Name varchar(25) YES YES YES goose egg cypher deceitful NULL end product shown by system Gender char(1) Marks1 decimal(4,1) YES ++++++-+ 4 rows in set (0. 01 sec) Ms.Sujata adds some rows in the educatee table using the INSERT INTO statement INSERT INTO pupil VALUES (1,Siddharth Sehgal,M,93) INSERT INTO student VALUES (2,Gurpreet Kaur,F,91) INSERT INTO Student VALUES (3,Monica genus genus Rana,F,93) INSERT INTO Student VALUES (4,Jatinder Sen,M,78) INSERT INTO Student VALUES (5,George Jacob,M,76) INSERT INTO Student VALUES (6,Deepa Bhandari,F,77) INSERT INTO Student VALUES (7,Akshay Nath,M,65) Changing Structure of table When we create a table we define its structure. We can also budge its structure i. e. add, remove or change its column(s) using the ALTER TABLE statement.Syntax ALTER TABLE ADD/DROP datatype ALTER TABLE MODIFY 216 INFORMATICS PRACTICES Chapter-9 MySQL Example Ms. Sujata adds a column named Games. mysql ALTER TABLE Student ADD Games VARCHAR(20) Now she wants to check the structure of the table to see that the new column Games is added. mysql DESCRIBE Student ++++++-+ Field Type Null Key Default Extra ++++++-+ Rollno int(11) Name varchar(25) YES YES YES NULL NULL N ULL NULL NULL Gender char(1) Marks1 decimal(4,1) YES Games varchar(20) YES ++++++-+ 5 rows in set (0. 00 sec) After execution of the above ALTER TABLE statement, the Games column is added and a NULL appreciate is assigned to all the rows in this column. mysql SELECT * FROM Student +++++-+ Rollno Name 1 Siddharth Sehgal 2 Gurpreet Kaur 3 Monica Rana 4 Jatinder Sen 5 George Jacob 6 Deepa Bhandari 7 Akshay Nath Gender Marks1 M F F M M F M 93. 91. 0 93. 0 78. 0 76. 0 77. 0 65. 0 Games NULL NULL NULL NULL NULL NULL NULL +++++-+ +++++-+ INFORMATICS PRACTICES 217 Chapter-9 MySQL Now, suppose we want to change the newly added Games column to hold integers(in place of character data) using ALTER TABLE statement mysql ALTER TABLE Student MODIFY games INTEGER To delete a column of a table the ALTER TABLE statement is used with Drop clause. Ms.Sujata deletes the Games column using the ALTER TABLE statement mysql ALTER TABLE Student DR OP Games mysql DESC student ++++++-+ Field Type Null Key Default Extra ++++++-+ Rollno int(11) Name varchar(25) YES YES YES NULL NULL NULL NULL Gender char(1) Marks1 decimal(4,1) YES ++++++-+ 4 rows in set (0. 00 sec) The above display shows that Games column is removed from the table. The word DESC can also be used in place of DESCRIBE Retrieving Information with SELECT Statement The SELECT statement is used to clear data from one or more database tables. Retrieving Single Column Here is the syntax of SELECT statement to retrieve a single column from a table Syntax SELECT FROM 218 INFORMATICS PRACTICES Chapter-9 MySQL Example Ms. Sujata wants to display Roll verse of all her students. She uses the following statement mysql SELECT Rollno FROM Student +- + Rollno +- + 1 2 3 4 5 6 7 - + 7 rows in set (0. 00 sec) Retrieving Multiple Columns We can display more than one column(s) from a table using SELECT statement Syntax SELECT , FR OM Example Now, Ms. Sujata displays two columns Roll meter and names of all the students. mysql SELECT Rollno, Name FROM Student +- ++ Rollno Name +- ++ 1 2 Siddharth Sehgal Gurpreet Kaur 219 INFORMATICS PRACTICES Chapter-9 MySQL 3 4 5 6 7 Monica Rana Jatinder Sen George Jacob Deepa Bhandari Akshay Nath +- ++ 7 rows in set (0. 00 sec) Changing the order of display of Columns We can display columns in any order by specifying the columns in that order in SELECT statement . The following statement displays Names first and then Roll numbers from the table Student. mysql SELECT Name,Rollno FROM Student +++ Name Rollno +++ Siddharth Sehgal Gurpreet Kaur Monica Rana Jatinder Sen George Jacob Deepa Bhandari Akshay Nath 1 2 3 4 5 6 7 ++ 7 rows in set (0. 00 sec) In the Output, notice that the first column displaying names is left(a)-justified and the due south column displaying roll numbers is right justified. The format of rig follows the expression that character data is left justified and numeric data is right justified. 220 INFORMATICS PRACTICES Chapter-9 MySQL Retrieving all Columns To see all the columns of the table, we can write * in place of names of all the columns. The columns are displayed in the order in which they are stored in the table. Ms.Sujata uses the following statement to see all the columns of her table mysql SELECT * FROM Student +++++ Rollno Name Gender Marks1 +++++ 1 2 3 4 5 6 7 Siddharth Sehgal Gurpreet Kaur Monica Rana Jatinder Sen George Jacob Deepa Bhandari Akshay Nath M F F M M F M 93. 0 91. 0 93. 0 78. 0 76. 0 77. 0 65. 0 +++++ 7 rows in set (0. 00 sec) The asterisk (*) means All.SELECT * means display all columns Eliminating duplicate take accounts By oversight data is displayed from all the rows of the table, even if the data in the result is duplicated. Using the keyword hard-hitting, the duplicate values can be eliminated in the result. When DIS TINCT keyword is specified, only one example of the duplicated data is shown. The following query without the DISTINCT keyword shows 7 rows era the same query with DISTINCT keyword shows 6 rows as duplicate data 93 is displayed only once. INFORMATICS PRACTICES 221 Chapter-9 MySQL mysql SELECT Marks1 FROM Student ++ Marks1 ++ 93. 91. 0 93. 0 78. 0 76. 0 77. 0 65. 0 93 displayed twice ++ 7 rows in set (0. 00 sec) mysql SELECT DISTINCT Marks1 FROM Student ++ Marks1 ++ 93. 0 91. 0 78. 0 76. 0 77. 0 65. 0 ++ 6 rows in set (0. 00 sec) 222 INFORMATICS PRACTICES Chapter-9 MySQL Retrieving Data From All Rows If we write the keyword all(a) in place of DISTINCT, then the result of SELECT query displays all the values including duplicate values. The yield is the same as what we get when we do not write DISTINCT keyword in the SELECT query.Using Arithmetic Operators with SELECT Arithmetic operators put to death mathematical calculations. In SQL the following arit hmetic operators are used Operator + * / % What it does Addition Subtraction Multiplication Division Modulus (or remainder) Modulus operator (%) saves the remainder of a division. We can execute simple arithmetic computations on data using SELECT statement. Ms. Sujata thinks what if all my students had secured 5 marks more. She enters the following statement to display marks of all students increase by 5. mysql SELECT Marks1+5 FROM Student +-+ Marks1+5 +-+ 98. 96. 0 98. 0 83. 0 81. 0 82. 0 70. 0 +-+ 7 rows in set (0. 02 sec) INFORMATICS PRACTICES 223 Chapter-9 MySQL Marks1 column is displayed increased by 5. The true values are not increased in the table. Here are some more examples mysql SELECT Name,Marks1+0. 05*Marks1 FROM Student mysql SELECT Name,Marks1-10 FROM Student mysql SELECT Name,Marks1/2 FROM Student Using these operators on tables does not create new columns in the tables or change the actual data values. The results of the calculations issue onl y in the output.In the above examples, arithmetic calculations were based on Student table. Arithmetic calculations may not always be based on tables. For example when we want to compute 7*3+1, there is no table to be referenced. In such queries no FROM clause is used mysql SELECT 7*3+1 +-+ 7*3+1 +-+ 22 +-+ 1 row in set (0. 09 sec) mysql SELECT 71+34 +-+ 71+34 +-+ 105 +-+ 1 row in set (0. 00 sec) 224 INFORMATICS PRACTICES Chapter-9 MySQL Using Column Alias bowl now, we have seen that when the result of an SQL statement is displayed, the heading displayed at the top of column is same s the column name in the table or the arithmetic operation being done on the Column. While displaying marks from the table Student, Ms. Sujata wants the output to display a column heading (for Marks) that is easier to understand and is more meaningful and presentable like Marks Secured instead of Marks1. Column alias lets different name (heading) to appear for a column than the actual one in th e output. She enters the statement like this mysql SELECT Marks1 AS Marks Secured FROM Student ++ Marks Secured ++ 93. 91. 0 93. 0 78. 0 76. 0 77. 0 65. 0 ++ 7 rows in set (0. 00 sec) Notice that the column Marks1 has been given the column heading Marks Secured . If a column alias consists of more than one word ,then it should be enclosed in quotes as in Marks Secured,otherwise error message is displayed. Using Column Alias does not rename a column. It simply displays a different column name in the output. The AS keyword between the column name and alias is optional. We can also write SELECT Marks1 Marks Secured FROM Student INFORMATICS PRACTICES 225Chapter-9 MySQL putt text in Query output Can Ms. Sujata make the query output more presentable by inserting items such as symbols or text in the query output ? Yes. She can. She uses the following statement. mysql SELECT Rollno,Name,has secured marks,marks1 FROM student +++-++ Rollno Name has secured marks marks1 + ++-++ 1 Siddharth Sehgal has secured marks 2 Gurpreet Kaur 3 Monica Rana 4 Jatinder Sen 5 George Jacob 6 Deepa Bhandari 7 Akshay Nath has secured marks has secured marks has secured marks has secured marks has secured marks has secured marks 93. 0 91. 0 93. 0 78. 0 76. 0 77. 0 65. 0 +++-++ 7 rows in set (0. 00 sec) The text has secured marks is displayed with every row of the table. Retrieving specific rows WHERE clause Tables usually contain some rows. Mostly, we do not want to display all the rows of a table. veritable rows can be displayed based on the criteria for selection of rows using the keyword WHERE. The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified criterion. Syntax SELECT , ,. FROM WHERE Ms. Sujata wants to display the names and marks of all those students who have secured marks above 80, she enters 226 INFORMATICS PRACTICES Chapter-9 MySQL mysql SELECT Name,Marks1 FROM Stud ent WHERE Marks1 80 ++-+ Name Marks1 ++-+ Siddharth Sehgal Gurpreet Kaur Monica Rana 93. 91. 0 93. 0 ++-+ 3 rows in set (0. 00 sec) She thinks What would be the marks of my students if they were increased by 5 for all those students who secured marks downstairs 80? She enters the statement mysql SELECT Name,Marks1+5 FROM Student WHERE marks1 = SELECT * FROM Student WHERE Marks1=93 +++++ Rollno Name Gender Marks1 +++++ 1 3 Siddharth Sehgal Monica Rana M F 93. 0 93. 0 +++++ 2 rows in set (0. 6 sec) When we use relational operators with character data type, means rather in the alphabet and means later in the alphabet. Aman Ayan as m comes before y in alphabet. Some more examples of queries involving relational expressions mysql SELECT Name,Marks1 FROM Student WHERE Marks1 SELECT * FROM Student WHERE Name = Gurpreet Kaur mysql SELECT RollNo,Marks1 FROM Student WHERE Rollno SELECT RollNo,Marks1 FROM Student WHERE Rollno 3 mysql SELECT RollNo,Marks1 FROM Student WHERE Name Mani Kumar 228 INFORMATICS PRACTICESChapter-9 MySQL Logical Operators OR, AND, not logical operators are used in SQL. Logical operators OR and AND are used to connect relational expressions in the WHERE clause. If any of the comparisons are true, OR returns TRUE. AND requires both conditions to be true in order to return TRUE. not negates a condition. If a condition returns a True value, adding not causes the condition to return a False value and vice versa. The symbol can be used in place of OR, && can be used in place of AND, can be used in place of NOT operator. Ms.Sujata uses the following statement (with Logical operator AND) to display Roll numbers and names of students who have secured marks above 70 but below 80. mysql SELECT Rollno, Name,Marks1 FROM Student WHERE Marks1 70 AND Marks1 80 ++-++ Rollno Name Marks1 ++-++ 4 5 6 Jatinder Sen George Jacob Deepa Bhandari 78. 0 76. 0 77. 0 ++-++ 3 rows in set (0. 01 sec) Some example of SQL sta tements with Logical operators are shown below. ysql SELECT Empnumber, EmpName FROM Employee WHERE Department = Accoumts OR Department = Personnel mysql SELECT Empnumber, EmpName FROM Employee WHERE Department = Accoumts AND Designation = Manager mysql SELECT Empnumber, EmpName FROM Employee WHERE NOT(Designation = Manager) mysql SELECT Name,TotalMarks FROM Candidate WHERE writtenmarks80 Interviewmarks10 SELECT Name,TotalMarks FROM Candidate WHERE writtenmarks80 && Interviewmarks10 INFORMATICS PRACTICES 229 Chapter-9 MySQL Using Parenthesis in WHERE clause Sometimes we have to write a criterion using a combination of AND and OR.The parentheses not only help us visually see how things are grouped together but they also let the DBMS know exactly what to do. SELECT * FROM Emp WHERE first_name=Amit AND (last_name=Sharma OR last_name=Verma) So, how does that work? It simply states that we are looking for anyone with the first name as Amit and the last name as Sharma or Verma. They must have the first name as Amit but can have the last name as either Sharma or Verma. Condition based on Range The among operator defines the regularise of values within which the column values must fall into to make the condition true. The range includes both the upper and lower values.Ms. Sujata uses the following statement to display roll numbers and marks of students who have secured marks in the range 70 to 80 (including 70 and 80). mysql SELECT Rollno,Name,Marks1 FROM Student WHERE Marks1 BETWEEN 70 AND 80 ++-++ Rollno Name Marks1 ++-++ 4 5 6 Jatinder Sen George Jacob Deepa Bhandari 78. 0 76. 0 77. 0 ++-++ 3 rows in set (0. 06 sec) The following statement displays roll numbers and marks of students who have secured marks other than the ones in the range 70 to 80(including 70 and 80). 30 INFORMATICS PRACTICES Chapter-9 MySQL mysql SELECT Rollno,Name,Marks1 FROM Student WHERE Marks1 NOT BETWEEN 70 AND 80 BETWEEN displays all values between the lower and the upper values including the lower and the upper values. To display marks in the range 70 to 80, Ms. Sujata could have used the following statement to give the same output as the one using BETWEEN operator. mysql SELECT Rollno,Name,Marks1 FROM Student WHERE Marks1=70 AND Marks1 SELECT Rollno, Name, Marks1 FROM Student WHERE Marks1 IN (68,76,78) +-+++ Rollno Name Marks1 -+++ 4 5 Jatinder Sen 78. 0 76. 0 George Jacob +-+++ 2 rows in set (0. 00 sec) In an Employee table, to display rows where State is DELHI or MUMBAI or UP, we write the query like this SELECT * FROM Employee WHERE State IN (DELHI,MUMBAI,UP) In an Employee table, to display all rows except those that have State as DELHI or MUMBAI or UP, we write the query like this INFORMATICS PRACTICES 231 Chapter-9 MySQL SELECT * FROM Employee WHERE State NOT IN (DELHI,MUMBAI,UP) Till now Ms.Sujatas table Student has 7 rows. She wants to lie in it with some more rows. She uses the following INSERT INTO statement. INSERT INTO Stud ent VALUES (8,Samdisha Sen,F,76) INSERT INTO Student VALUES (9,Geeta Sen Sharma,F,91) INSERT INTO Student VALUES (10,Geet Kadamb,M,66) INSERT INTO Student VALUES (11,Aman Ali,M,92) INSERT INTO Student VALUES (12,Ayan Ali,M,87) She checks that the table has the new rows inserted by using the following SELECT statement SELECT * FROM Student +++++ Rollno name Gender Marks1 ++++ 1 2 3 4 5 6 7 8 9 10 11 12 Siddharth Sehgal Gurpreet Kaur Monica Rana Jatinder Sen George Jacob Deepa Bhandari Akshay Nath Samdisha Sen Geeta Sen Sharma Geet Kadamb Aman Ali Ayan Ali M F F M M F M F F M M M 93 91 93 78 76 77 65 76 91 66 92 87 +++++ 12 rows in set (0. 00 sec) 232 INFORMATICS PRACTICESChapter-9 MySQL Condition based on pattern matches Sometimes while trying to remember somebodys name, you remember a part of his/her name but not the exact name. In such cases, MySQL has rattling(a)cards to help you out. % and _ are two wild card chara cters. The share (%) symbol is used to represent any sequence of zero or more characters. The underscore (_) symbol is used to represent a single character. LIKE clause is used to fetch data which matches the specified pattern from a table.The LIKE clause tells the DBMS that we wont be doing a strict comparison like = or or but we will be using wildcards in our comparison. Syntax SELECT , WHERE LIKE Pattern AND OR For example, Ms. Sujata wants to display details of students who have their names culmination with Sen, she enters mysql SELECT * FROM Student WHERE Name LIKE %Sen ++++-+ Rollno Name Gender Marks1 ++++-+ 4 Jatinder Sen M 8 Samdisha Sen F 78. 76. 0 ++++-+ 2 rows in set (0. 00 sec) To display rows from the table Student with names jump with G, she enters mysql SELECT * FROM Student WHERE Name LIKE G% ++++-+ Rollno name Gender Marks1 ++++-+ 2 Gurpreet Kaur F 91. 0 233 INFORMATICS PRACTICES Chapter-9 MySQL 5 George Jacob 9 Geeta Sen Sharma 1 0 Geet Kadamb M F M 76. 0 91. 66. 0 ++++-+ 4 rows in set (0. 02 sec) To display rows that have names starting with G and ending with b, she enters mysql SELECT * FROM Student WHERE Name LIKE G%b +-++-++ Rollno name Gender Marks1 +-++-++ 5 10 George Jacob Geet Kadamb M M 76. 0 66. 0 +-++-++ 2 rows in set (0. 0 sec) To display rows from the table Student that have Sen anywhere in their names, she enters mysql SELECT * FROM Student WHERE Name LIKE %Sen% +-++-++ Rollno name Gender Marks1 +-++-++ 4 8 9 Jatinder Sen Samdisha Sen Geeta Sen Sharma M F F 78 76 91 +-++-++ 3 rows in set (0. 00 sec) 234 INFORMATICS PRACTICES Chapter-9 MySQLTo display rows that have names starting with A and then having any 4 characters and ending with Ali, she uses underscore wild card like this mysql SELECT * FROM Student WHERE Name LIKE A_ _ _ _ Ali +-++-++ Rollno name Gender Marks1 +-++-++ 11 12 Aman Ali Ayan Ali M M 92. 0 87. 0 +-++-++ 2 rows in set (0. 00 s ec) Some more examples Am% matches any string starting with Am. %Singh% matches any string containing Singh %a matches any string ending with a _ _ _ matches any string that is exactly 3 characters long. _ _ % matches any string that has at least 2 characters. _ _ _ g matches any string that is 4 characters long with any 3 characters in the beginning but g as the 4th character. The keyword NOT LIKE is used to select the rows that do not match the specified pattern. To display rows from the table Student that have names not starting with G, she enters mysql SELECT * FROM Student WHERE Name NOT LIKE G% antecedence of Operators All the operators have antecedence.Precedence is the order in which different operators are evaluated in the same expression. When evaluating an expression containing INFORMATICS PRACTICES 235 Chapter-9 MySQL multiple operators, operators with higher precedence are evaluated before evaluating those with lower precedence. Operators with equal precedence are eva luated from left to right within the expression. Parenthesis can be used to change the preference of an operator. Various operators in descending order of precedence (top to bottom) are listed below (unary minus) *, /, DIV, %, MOD -, + =, , =, , INSERT INTO Student(Rollno,Marks1) VALUES (14,45) Query OK, 1 row affected (0. 05 sec) Since values are provided only for Roll number and marks, Ms. Sujata uses the SELECT statement and notices the word NULL displayed for Name and Gender for Roll number 14 mysql SELECT * FROM Student WHERE Rollno =14 ++++-+ Rollno name Gender Marks1 ++++-+ 14 NULL NULL 45. 0 ++++-+ 1 row in set (0. 0 sec) Explicitly Inserting NULL Values We have learnt that if a column can hold NULL values, it can be omitted from the INSERT INTO statement. INSERT INTO statement will automatically insert a visionary value in that column. This is called Implicitly inserting a NULL value. mysql INSERT INTO Student(Rollno,Name,Gender) Values(15,Charvi Chanana,F) Qu ery OK, 1 row affected (0. 11 sec) In the above INSERT INTO statement Marks1 column is omitted, therefore NULL value will be inserted for it. We can also explicitly add NULL value by using the NULL keyword in the VALUES list for those columns that can hold null values. ysql INSERT INTO Student Values(14,Siddharth Sehgal,M,NULL) Query OK, 1 row affected (0. 11 sec) 242 INFORMATICS PRACTICES Chapter-9 MySQL A NULL value means no value has been entered for that column i. e. the value for that column is not known. Inserting Date Values The default way to store a date in MySQL is with the type DATE. at a lower place is the format of a DATE. YYYY-MM-DD To insert the current date into a table, MySQLs constitutive(a) function CURDATE() can be used in the query. Following are some examples of inserting date values. ysql INSERT INTO my_table (idate) VALUES (19970505) mysql INSERT INTO my_table (idate) VALUES (97-05-05) mysql INSERT INTO my_table (idate) VALUES (1997. 05. 05) mysql INSERT I NTO my_table (idate) VALUES (0000-00-00) While Inserting data v Text values must be enclosed in quotes. v Standard date format is yyyy-mm-dd. v Standard time format is hhmmss. v are required around the standard date and time formats. Quotes modify control In the table student, Ms. Sujata entered a students marks as 93.Suppose, that student found out that one of her answers was unchecked and got her marks increased by 1. How would Ms. Sujata change it in the table? She can use the update statement to modify existing data in the table. (a) Syntax modify association = , = , WHERE INFORMATICS PRACTICES 243 Chapter-9 MySQL The statement can be used to update one or more columns together. WHERE clause helps in updation of particular rows in a table. The following statement sets the marks(Mark1) of all the rows to 94. UPDATE Student tick Marks1 = 94 The following statement sets the marks(Mark1) of the row with name as Monica Rana to 94. ysql UPDATE Student SET Marks1 = 94 WHERE na me = Monica Rana Query OK, 1 row affected (0. 03 sec) Rows matched 1 Changed 1 Warnings 0 The marks displayed from the table shows 94 marks now mysql SELECT Name,Marks1 FROM Student WHERE Name = Monica Rana Output +++ Name Marks1 +++ Monica Rana 94 +++ 1 row in set (0. 00 sec) What if Ms. Sujata wants to change the name and marks both at the same time? Multiple columns can also be updated at one time.The following statement changes the name to Chhavi Chanana and Marks to 90 for the roll number 15. mysql UPDATE Student SET name = Chhavi Marks1= 90 WHERE Rollno = 15 Output Query OK , 1 row affected (0. 8 sec) 244 INFORMATICS PRACTICES Chanana, Chapter-9 MySQL scratch STATEMENT Sometimes students leave school or an employee leaves an organization. Their rows have to be deleted from the table. Deleting data from a table is very simple. cancel statement is used to delete rows from a table. wipe out removes the entire row, not the individual column values. concern must be taken w hile using this statement as accidentally important data may get deleted. Syntax mysql DELETE FROM tablename Where condn One of the students with Roll number 14 has left the school and Ms. Sujata wants to delete his/her row. She uses the following statement to delete the row with roll number 14. mysql DELETE FROM Student WHERE Rollno = 14 Query OK, 1 row affected (0. 03 sec) DELETE statement can be used to delete all rows of the table also . The following statement can be used to delete all the rows from Student table. ysql DELETE from Student mysql Select * FROM Student ++++-+ Rollno Name Gender Marks1 ++++-+ ++++-+ 0 row in set (0. 01 sec) Know more The MySQL database management system contains an enormous amount of functionality and power. Using a simple set of statements for inserting, retrieving, deleting and updating data, we can develop quite a useful set of databases and tables. To learn more about MySQL you may visit the website http//www. mysqltutorial. org INF ORMATICS PRACTICES 245 Chapter-9 MySQLSummary 1. 2. 3. 4. 5. 6. 7. 8. CREATE DATABASE statement is used to create a new database. CREATE TABLE statement is used to create a new table. INSERT INTO statement is used to insert a new row in a table. The SELECT statement is used to fetch data from one or more database tables. SELECT * means display all columns. The WHERE clause is used to select specific rows. The DESCRIBE statement is used to see the structure of a table. We can change the structure of a table ie. add, remove or change its column(s) using the ALTER TABLE statement. The keyword DISTINCT is used to eliminate redundant data from display. a) Logical operators OR and AND are used to connect relational expressions in the WHERE clause. Logical operator NOT is used to negate a condition. 9. 10. (b) 11. The BETWEEN operator defines the range of values that the column values must fall into to make the condition true. The IN operator selects values that match any value in the give n list of values. % and _ are two wild card characters. The percent (%) symbol is used to represent any sequence of zero or more characters. The underscore (_) symbol is used to represent a single character. NULL represents a value that is unavailable, unassigned, unknown or inapplicable.The results of the SELECT statement can be displayed in the ascending or descending order of a single column or columns using determine BY clause. UPDATE statement is used to modify existing data in a table. DELETE statement is used to delete rows from a table. 12. 13. 14. 15. 16. 17. 246 INFORMATICS PRACTICES Chapter-9 MySQL Multiple Choice questions 1. Which statement is used to extract data from a table? A. B. C. D. 2. SELECT DISPLAY READ EXTRACT How do you select all the columns from a table named Employee? A. B. C. D. SELECT all FROM Employee SELECT Employee SELECT * BY Employee SELECT * FROM Employee . How do you select a column named IName from a table named parentage? A. B. C. D. SELECT I nventory FROM Iname DISPLAY Iname SELECT Iname FROM Inventory FROM Inventory SELECT Iname, Inventory FROM Iname 4. Which of the following are valid column names? A. B. C. D. Marks Eng 66_Marks Marks_Eng Eng_Marks 5. SELECT statement can be used to perform these functions. A. B. C. D. Insert rows into a table. Choose and display columns from a table. Modify data in a table. Select and display structure of a table INFORMATICS PRACTICES 247 Chapter-9 MySQL 6. Which statement is used to insert new data in a table?A. B. C. D. ADD RECORD INSERT RECORD INSERT INTO INSERT form 7. How would you display all those rows from a table named Friends where the value of the column Hobbies is SWIMMING A. B. C. D. SELECT ALL FROM Friends WHERE Hobbies IS SWIMMING SELECT * FROM Friends WHERE Hobbies=SWIMMING SELECT * FROM Friends WHERE Hobbies = melted SELECT ALL FROM Friends WHERE Hobbies SWIMMING 8. Which statement is used to modify data in a table? A. B. C. D. CHANGE MODIFY UPDATE SAVE AS 9. Whi ch SQL statement is used to delete data from a table? A. B. C. D. DELETE DROP TRUNCATE REMOVE 10.How do you select all the rows from a table named Student where the value of the column FName starts with G? A. B. C. D. SELECT * FROM Student WHERE FName LIKE G_ SELECT * FROM Student WHERE FName=G SELECT * FROM Student WHERE FName LIKE G% SELECT * WHERE Student WHERE FName=%G% INFORMATICS PRACTICES 248 Chapter-9 MySQL 11. The OR operator displays a record if ANY of the conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true A. B. False True 12. Which keyword is used to return only different values in a column? A. B. C. D. DIFFERENT scoop DISTINCT UNIQUE 3. Which SQL keyword(s) is/are used to sort the rows in the output A. B. C. D. secernateED ORDER SORT SORT BY ORDER BY 14. How would you return all the rows from a table named Item sorted in descending order on the column IName? A. B. C. D. SELECT * FROM Item SORT IName DESC SELECT * FROM Item ORDER BY IName DESC SELECT * FROM Item ORDER IName DESC SELECT * FROM Item SORT BY IName DESC 15. How can you insert a new row into the Store table? A. B. C. D. INSERT (1,Abc Rice) INTO Store INSERT VALUES (1,Abc Rice) INTO Store INSERT INTO Store VALUES (1,Abc Rice) ADD ROW Store values(1,Abc Rice)INFORMATICS PRACTICES 249 Chapter-9 MySQL 16. Which statement is appropriate to change the first name Madhur to Mridul in the FName column in the Student table? A. B. C. D. UPDATE Student SET FName=Mridul WHERE FName=Madhur MODIFY Student SET FName=Madhur INTO FName=Mridul UPDATE Student SET FName=Madhur INTO FName=Mridul UPDATE Student SET FName=Madhur WHERE FName=Mridul 17. How can you delete the rows with marks below 33 in the Student Table? A. B. C. D. DELETE FROM Student WHERE marks