WIXLIB

Once data types and constraints are identified, let uscreate tables without specifying constraints along withthe attribute name for simplification. We will learn toincorporate constraints on attributes in Section 9.4.4.Example 9.1 Create table STUDENT.mysql CREATE TABLE STUDENT(- RollNumber INT,- SName VARCHAR(20),- SDateofBirth DATE,- GUID CHAR (12),- PRIMARY KEY (RollNumber));Query OK, 0 rows affected (0.91 sec)Which datatype outof Char and Varcharwill you prefer forstoring contactnumber(mobilenumber)? Discuss.Note: “,” is used to separate two attributes and each statementterminates with a semi-colon (;). The arrow (- ) is an interactivecontinuation prompt. If we enter an unfinished statement, the SQLshell will wait for us to enter the rest of the statement.9.4.3 Describe TableWe can view the structure of an already created tableusing the DESCRIBE statement or DESC statement.Syntax:DESCRIBE tablename;mysql DESCRIBE STUDENT; -------------- ------------- ------ ----- --------- ------- Field Type Null Key Default Extra -------------- ------------- ------ ----- --------- ------- RollNumber int NO PRI NULL SName varchar(20) YES NULL SDateofBirth date YES NULL GUID char(12) YES NULL -------------- ------------- ------ ----- --------- ------- 4 rows in set (0.06 sec)We can use the SHOW TABLES statement to see thetables in the StudentAttendance database. So far, wehave only the STUDENT table.mysql SHOW TABLES; ------------------------------ Tables in studentattendance ------------------------------ student ------------------------------ 1 row in set (0.00 sec)9.4.4 ALTER TableActivity 9.4Create the other tworelations GUARDIANand ATTENDANCE asper data types givenin Table 9.4 and 9.5respectively, andview their structures.Do not add anyconstraint inthese twotables.After creating a table, we may realise that we need toadd/remove an attribute or to modify the datatype ofan existing attribute or to add constraint in attribute. InSTRUCTURED QUERY LANGUAGE (SQL)1372022-23Chpater-9.indd 13718-Jun-21 2:35:45 PM

all such cases, we need to change or alter the structure(schema) of the table by using the alter statement.(A) Add primary key to a relationLet us now alter the tables created in Activity 9.4. Thefollowing MySQL statement adds a primary key to theGUARDIAN relation:mysql ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);Query OK, 0 rows affected (1.14 sec)Records: 0 Duplicates: 0 Warnings: 0Now let us add the primary key to the ATTENDANCErelation. The primary key of this relation is a compositekey made up of two attributes - AttendanceDate andRollNumber.Activity 9.5Add foreign key in theATTENDANCE table(use Figure 9.1) toidentify referencingand referencedtables).mysql ALTER TABLE ATTENDANCE- ADD PRIMARY KEY(AttendanceDate,RollNumber);Query OK, 0 rows affected (0.52 sec)Records: 0 Duplicates: 0 Warnings: 0(B) Add foreign key to a relationOnce primary keys are added, the next step is to addforeign keys to the relation (if any). Following points needto be observed while adding foreign key to a relation: The referenced relation must be already created. The referenced attribute(s) must be part of theprimary key of the referenced relation. Data types and size of referenced and referencingattributes must be the same.Syntax:ALTER TABLE table name ADD FOREIGN KEY(attributename) REFERENCES referenced table name(attribute name);Name foreign keys intable ATTENDANCEand STUDENT. Isthere any foreign keyin table GUARDIAN.Let us now add foreign key to the table STUDENT.Table 9.3 shows that attribute GUID (the referencingattribute) is a foreign key and it refers to attribute GUID(the referenced attribute) of table GUARDIAN. Hence,STUDENT is the referencing table and GUARDIAN is thereferenced table as shown in Figure 8.4 in the previouschapter.mysql ALTER TABLE STUDENT- ADD FOREIGN KEY(GUID) REFERENCES- GUARDIAN(GUID);Query OK, 0 rows affected (0.75 sec)Records: 0 Duplicates: 0 Warnings: 0(C) Add constraint UNIQUE to an existing attributeIn GUARDIAN table, the attribute GPhone has aconstraint UNIQUE which means no two values in thatcolumn should be the same.COMPUTER SCIENCE - CLASS XII1382022-23Chpater-9.indd 13818-Jun-21 2:35:45 PM

Syntax:ALTER TABLE table name ADD UNIQUE (attributename);NOTESLet us now add the constraint UNIQUE with theattribute GPhone of the table GUARDIAN as shown attable 9.4.mysql ALTER TABLE GUARDIAN- ADD UNIQUE(GPhone);Query OK, 0 rows affected (0.44 sec)Records: 0 Duplicates: 0 Warnings: 0(D) Add an attribute to an existing tableSometimes, we may need to add an additional attributein a table. It can be done using the ADD attributestatement as shown in the following Syntax:ALTER TABLE table name ADD attributename DATATYPE;Suppose, the principal of the school has decided toaward scholarship to some needy students for whichincome of the guardian must be known. But, the schoolhas not maintained the income attribute with tableGUARDIAN so far. Therefore, the database designernow needs to add a new attribute Income of data typeINT in the table GUARDIAN.mysql ALTER TABLE GUARDIAN- ADD income INT;Query OK, 0 rows affected (0.47 sec)Records: 0 Duplicates: 0 Warnings: 0(E) Modify datatype of an attributeWe can change data types of the existing attributes of atable using the following ALTER statement.Syntax:ALTER TABLE table name MODIFY attribute DATATYPE;Suppose we need to change the size of the attributeGAddress from VARCHAR(30) to VARCHAR(40) of theGUARDIAN table. The MySQL statement will be:mysql ALTER TABLE GUARDIAN- MODIFY GAddress VARCHAR(40);Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0(F) Modify constraint of an attributeWhen we create a table, by default each attribute takesNULL value except for the attribute defined as primarykey. We can change an attribute’s constraint from NULLto NOT NULL using an alter statement.STRUCTURED QUERY LANGUAGE (SQL)1392022-23Chpater-9.indd 13918-Jun-21 2:35:45 PM

Syntax:What are theminimum andmaximum incomevalues that can beentered in the incomeattribute given thedata type is INT?ALTER TABLE table name MODIFY attribute DATATYPENOT NULL;Note: We have to specify the data type of the attribute along withconstraint NOT NULL while using MODIFY.To associate NOT NULL constraint with attributeSName of table STUDENT (table 9.3), we write thefollowing MySQL statement:mysql ALTER TABLE STUDENT- MODIFY SName VARCHAR(20) NOT NULL;Query OK, 0 rows affected (0.47 sec)Records: 0 Duplicates: 0 Warnings: 0(G) Add default value to an attributeIf we want to specify default value for an attribute, thenuse the following syntax:ALTER TABLE table name MODIFY attribute DATATYPEDEFAULT default value;To set default value of SDateofBirth of STUDENT to15th May 2000, write the following statement:mysql ALTER TABLE STUDENT- MODIFY SDateofBirth DATE DEFAULT ‘2000-0515’;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0Note: We have to specify the data type of the attribute along withDEFAULT while using MODIFY.(H) Remove an attributeUsing ALTER, we can remove attributes from a table, asshown in the following syntax:ALTER TABLE table name DROP attribute;To remove the attribute income from tableGUARDIAN (Table 9.4), write the following MySQLstatement:mysql ALTER TABLE GUARDIAN DROP income;Query OK, 0 rows affected (0.42 sec)Records: 0 Duplicates: 0 Warnings: 0(I) Remove primary key from the tableSometime there may be a requirement to remove primarykey constraint from the table. In that case, Alter tablecommand can be used in the following way:Syntax:ALTER TABLE table name DROP PRIMARY KEY;To remove primary key of table GUARDIAN (Figure9.4), write the following MySQL statement:mysql ALTER TABLE GUARDIAN DROP PRIMARY KEY;Query OK, 0 rows affected (0.72 sec)Records: 0 Duplicates: 0 Warnings: 0COMPUTER SCIENCE - CLASS XII1402022-23Chpater-9.indd 14018-Jun-21 2:35:45 PM

Note: We have dropped the primary key from the GUARDIAN table,but each table should have a primary key to maintain uniqueness.Hence, we have to use the ADD statement with the Alter Tablecommand to specify the primary key for the GUARDIAN table asshown in earlier examples.NOTES9.4.5 DROP StatementSometimes a table in a database or the database itselfneeds to be removed. We can use a DROP statementto remove a database or a table permanently from thesystem. However, one should be very cautious whileusing this statement as it cannot be undone.Syntax to drop a table:DROP TABLE table name;Syntax to drop a database:DROP DATABASE database name;Note: Using the DROP statement to remove a database willultimately remove all the tables within it.9.5 SQLFORDATA MANIPULATIONIn the previous section, we created the databaseStudentAttendance having three relations STUDENT,GUARDIAN and ATTENDANCE. When we create a table,only its structure is created but the table has no data.To populate records in the table, INSERT statement isused. Also, table records can be deleted or updated usingDELETE and UPDATE statements. These SQL statementsare part of Data Manipulation Language (DML).Data Manipulation using a database means eitherinsertion of new data, removal of existing data ormodification of existing data in the database9.5.1 INSERTION of RecordsINSERT INTO statement is used to insert new records ina table. Its syntax is:INSERT INTO tablenameVALUES(value 1, value 2,.);Here, value 1 corresponds to attribute 1, value 2corresponds to attribute 2 and so on. Note that we neednot to specify attribute names in the insert statementif there are exactly the same numbers of values in theINSERT statement as the total number of attributes inthe table.Caution: While populating records in a table with foreignkey, ensure that records in referenced tables are alreadypopulated.STRUCTURED QUERY LANGUAGE (SQL)1412022-23Chpater-9.indd 14118-Jun-21 2:35:45 PM

Let us insert some records in the StudentAttendancedatabase. We shall insert records in the GUARDIANtable first as it does not have any foreign key. A setof sample records for GUARDIAN table is shown in thegiven table (Table 9.6).Table 9.6 GUARDIAN TableGUIDGNameGPhoneGAddress444444444444Amit Ahuja5711492685G-35, Ashok Vihar, Delhi111111111111Baichung Bhutia7110047139Flat no. 5, Darjeeling Appt., Shimla101010101010Himanshu Shah981818485526/77, West Patel Nagar, Ahmedabad333333333333Danny Dsouza466444444666Sujata P.S -13, Ashok Village, Daman7802983674HNO-13, B- block, Preet Vihar, MaduraiThe following insert statement adds the first recordin the table:mysql INSERT INTO GUARDIAN- VALUES (444444444444, 'Amit Ahuja',- 5711492685, 'G-35,Ashok vihar, Delhi' );Query OK, 1 row affected (0.01 sec)We can use the SQL statement SELECT * fromtable name to view the inserted records. The SELECTstatement will be explained in the next section.mysql SELECT * from GUARDIAN; -------------- --------------- ------------ --------------------------- GUID GName Gphone GAddress -------------- --------------- ------------ --------------------------- 444444444444 Amit Ahuja 5711492685 G-35, Ashok vihar, Delhi -------------- --------------- ------------ --------------------------- 1 row in set (0.00 sec)Activity 9.6Write SQL statementsto insert the remaining3 rows of table9.6 in tableGUARDIAN.If we want to insert values only for some of theattributes in a table (supposing other attributes havingNULL or any other default value), then we shall specifythe attribute names in which the values are to be insertedusing the following syntax of INSERT INTO statement.Syntax:INSERT INTO tablename (column1, column2, .)VALUES (value1, value2, .);To insert the fourth record of Table 9.6 where GPhoneis not given, we need to insert values in the other threefields (GPhone was set to NULL by default at the timeof table creation). In this case, we have to specify thenames of attributes in which we want to insert values.The values must be given in the same order in whichattributes are written in INSERT statement.COMPUTER SCIENCE - CLASS XII1422022-23Chpater-9.indd 14218-Jun-21 2:35:45 PM

mysql INSERT INTO GUARDIAN(GUID, GName, GAddress)- VALUES (333333333333, 'Danny Dsouza',- 'S -13, Ashok Village, Daman' );Query OK, 1 row affected (0.03 sec)Note: Text and date values must be enclosed in ‘ ’ (single quotes).mysql SELECT * from GUARDIAN; -------------- -------------- ----------- --------------------------- GUID GName Gphone GAddress -------------- -------------- ----------- --------------------------- 333333333333 Danny Dsouza NULL S -13, Ashok Village,Daman 444444444444 Amit Ahuja 5711492685 G-35, Ashok vihar, Delhi -------------- -------------- ----------- --------------------------- 2 rows in set (0.00 sec)Let us now insert the records given in Table 9.7 intothe STUDENT table.Table 9.7 STUDENT TableRollNumberSNameSDateofBirthGUID1Atharv Ahuja2003-05-154444444444442Daizy Bhutia2002-02-281111111111113Taleem Shah2002-02-284John Dsouza2003-08-183333333333335Ali Shah2003-07-051010101010106Manika P.2002-03-10466444444666To insert the first record of Table 9.7, we write thefollowing MySQL statementmysql INSERT INTO STUDENT- VALUES(1,'Atharv Ahuja','2003-05-15',444444444444);Query OK, 1 row affected (0.11 sec)ORmysql INSERT INTO STUDENT (RollNumber, SName,SDateofBirth, GUID)- VALUES (1,'Atharv Ahuja','2003-05-15',444444444444);Query OK, 1 row affected (0.02 sec)Activity 9.7Write SQL statementsto insert theremaining 4 rowsof table 9.7 intable STUDENT.Recall that Date is stored in ‘YYYY-MM-DD’ format.mysql SELECT * from STUDENT; ------------ -------------- -------------- -------------- RollNumber SName SDateofBirth GUID ------------ -------------- -------------- -------------- 1 Atharv Ahuja 2003-05-15 444444444444 ------------ -------------- -------------- -------------- 1 row in set (0.00 sec)Let us now insert the third record of Table 9.7 whereGUID is NULL. Recall that GUID is foreign key of thistable and thus can take NULL value. Hence, we can putNULL value for GUID and insert the record by using thefollowing statement:STRUCTURED QUERY LANGUAGE (SQL)1432022-23Chpater-9.indd 14318-Jun-21 2:35:45 PM

mysql INSERT INTO STUDENT- VALUES(3, 'Taleem Shah','2002-02-28', NULL);Query OK, 1 row affected (0.05 sec)mysql SELECT * from STUDENT; ------------ -------------- -------------- -------------- RollNumber SName SDateofBirth GUID ------------ -------------- -------------- -------------- 1 Atharv Ahuja 2003-05-15 444444444444 3 Taleem Shah 2002-02-28 NULL ------------ -------------- -------------- -------------- 2 rows in set (0.00 sec) Which of the twoinsert statementshould be usedwhen the order ofdata to be insertedare not known? Can we insert tworecords with thesame roll number?We had to write NULL in the above insert statementbecause we are not mentioning the column names.Otherwise, we should mention the names of attributesalong with the values if we need to insert data only forcertain attributes, as shown in the following query:mysql INSERT INTO STUDENT (RollNumber, SName,- SDateofBirth) VALUES (3, 'Taleem Shah','2002-0228');Query OK, 1 row affected (0.05 sec)9.6 SQLFORDATA QUERYSo far we have learnt how to create a database and howto store and manipulate data in them. We are interestedin storing data in a database as it is easier to retrievedata in future from databases in whatever way we want.SQL provides efficient mechanisms to retrieve datastored in multiple tables in MySQL database (or anyother RDBMS). The SQL statement SELECT is used toretrieve data from the tables in a database and is alsocalled a query statement.9.6.1 SELECT StatementThe SQL statement SELECT is used to retrieve datafrom the tables in a database and the output is alsodisplayed in tabular form.Syntax:SELECT attribute1, attribute2, .FROM table nameWHERE condition;Here, attribute1, attribute2, . are the column namesof the table table name from which we want to retrievedata. The FROM clause is always written with SELECTclause as it specifies the name of the table from whichdata is to be retrieved. The WHERE clause is optional andis used to retrieve data that meet specified condition(s).COMPUTER SCIENCE - CLASS XII1442022-23Chpater-9.indd 14418-Jun-21 2:35:45 PM

To select all the data available in a table, we use thefollowing select statement:SELECT * FROM table name;Example 9.2 The following query retrieves the name anddate of birth of student with roll number 1:mysql SELECT SName, SDateofBirth- FROM STUDENT- WHERE RollNumber 1; -------------- -------------- SName SDateofBirth -------------- -------------- Atharv Ahuja 2003-05-15 -------------- -------------- 1 row in set (0.03 sec)Think and list fewexamples from yourdaily life wherestoring the data inthe database andquerying the samecan be helpful.9.6.2 QUERYING using Database OFFICEOrganisations maintain databases to store data in theform of tables. Let us consider the database OFFICEof an organisation that has many related tables likeEMPLOYEE, DEPARTMENT and so on. Every EMPLOYEEin the database is assigned to a DEPARTMENT andhis/her Department number (DeptId) is stored as aforeign key in the table EMPLOYEE. Let us consider therelation ‘EMPLOYEE’ as shown in Table 9.8 and applythe SELECT statement to retrieve data:Table 9.8 Records to be inserted into the EMPLOYEE bha42000D04110Tanya50000467D05(A) Retrieve selected columnsThe following query selects employee numbers of all theemployees:mysql SELECT EmpNo FROM ------- EmpNo ------- 101 102 EMPLOYEE;STRUCTURED QUERY LANGUAGE (SQL)1452022-23Chpater-9.indd 14518-Jun-21 2:35:45 PM

NOTES 103 104 105 106 107 108 109 110 ------- 10 rows in set (0.41 sec)The following query selects the employee numberand employee name of all the employees, we write:mysql SELECT EmpNo, Ename FROM ------- ---------- EmpNo Ename ------- ---------- 101 Aaliya 102 Kritika 103 Shabbir 104 Gurpreet 105 Joseph 106 Sanya 107 Vergese 108 Nachaobi 109 Daribha 110 Tanya ------- ---------- 10 rows in set (0.00 sec)EMPLOYEE;(B) Renaming of columnsIn case we wa

STRUCTURED QUERY LANGUAGE (SQL) 133 SQL is case insensitive. For example, the column names 'salary' and 'SALARY' are the same for SQL. Always end SQL statements with a semicolon (;). To enter multiline SQL statements, we don't write ";" after the first line. We press the Enter key to continue on the next line.