Class 12th_lT_Junior software developer.pdf

 Junior Software Development Developer (Job Role) Qualification Pack: Ref. Id. SSC/Q0508) Sector: Information Technology- Information Technology Enables Services (IT-ITeS) Textbook for C...

 Junior Software Development Developer (Job Role) Qualification Pack: Ref. Id. SSC/Q0508) Sector: Information Technology- Information Technology Enables Services (IT-ITeS) Textbook for Class XII PSS CENTRAL INSTITUTE OF VOCATIONAL EDUCATION Shyamla Hills, Bhopal – 462 002, M.P., India www.psscive.ac.in Page 2 of 200 TEXTBOOK DEVELOPMENT COMMITTEE MEMBERS 1. Akshay Sharam, Govt. Hss Girls Narsinghgarh, Rajgarh, AISECT. 2. Aakrati Verma, Govt. Subhash Excellence, Bhopal, AISECT. 3. Amit Sondhiya, Govt. HSS Garh, Rewa, ICA. 4. Prakash Deshmukh, Govt. Hss Dharnakala, Seoni, Satyam. 5. Shashank Dhuper, Govt. HSS Girls PT. Ravishankar Sagar, Satyam. Job Role – Junior Software Development for Class-XII. There are 05 Vocational Trainers from SAMGRA SIKSHA ABHIYAN (Secondary Education) Bhopal M.P. attached to PSSCIVE Bhopal for 05 days between 5 June 2023 to 9 June 2023. GUIDELINE MEMBER Prerna Kumari, Assistant Professor (Contractual), Department of Engineering and Technology, PSSCIVE, NCERT, Bhopal. Page 3 of 200  CONTENTS Unit1. Relational Database Management System Chapter 1. RDBMS Concepts Chapter 2. Structured Query Language (SQL) Chapter 3. FUNCTIONS IN SQL Unit 2. Advanced Python Programming Chapter 4. File Handling in Python Chapter 5. Exception Handling in Python Chapter 6. Implement data structure using python Unit 3. Software Engineering Chapter 7. Software Engineering Concepts Chapter 8. Software Testing Chapter 9. Software project using python Chapter 10. Contact Management System Unit 4. Emerging Trends and Social Impact Chapter 11. Emerging Trends and New Technology Chapter 12. Social impact on technology Page 4 of 200  Unit1. Relational Database Management System Chapter 1. RDBMS Concepts Kushal was playing with his father’s mobile phone. Accidentally all the contacts on the phone were deleted by him. To recover these contacts his father consulted a technician. The technician recovered all the contacts available using the Google contacts as these contacts are linked with the Google account. Thus, it is possible to save and manage the contacts in a smartphone. This is possible with the use of database applications available with Google. The data can be saved and managed through database application. In this session, you will understand the concept of the database and how data is organised in the database. Data models and the various concepts associated with database application such as constraints, primary key, foreign key is also discussed. There are various database management software available in the market. We will discuss MySQL which is a popular Relational Database Management System (RDBMS). 1.1 INTRODUCTION TO DATABASE SYSTEMS The word data is taken from "Datum", which means raw facts. Datum is a single piece of factual information of interest to us. Data, the plural of datum, is a collection of information. Data is the name given to basic raw facts and entities such as names, numbers and quantity. Its data can be defined as a collection of facts and records. Data, representing facts, figures, and ideas, are commonly used in everyday life. it needs to be managed to use it effectively. Data items are organised or processed to produce the information. It can be used for processing some useful information from it. The examples of data are weights, prices, costs, numbers of items sold, employee names, product names, addresses, tax codes, registration fees, obtained marks, reservation details, images, sounds, multimedia and animated data. Data can exist in the form of text, graphics, sound, video that represents every kind of information. The data items can be stored manually in a diary. But it is difficult to retrieve and process the data items when there are a large number of data items. Through the relational database systems, users can access a view of data called relations. With Relational Database Management Systems (RDBMS), programmers can perform database operations without knowing data storage details. It is observed that the schools are maintaining the student data and that is stored in the register. After several years of leaving the school, the student can get the duplicate of the school leaving certificate. For issuing such certificates the office staff check the student data from the registers which is maintained year-wise and class-wise. By checking the student record, the office staff can easily give the certificate to the student. This is how the schools are maintaining the student data in the register. The office staff also manually maintain student details who are presently learning. Their Admission number, Name, Date of Birth, Address, Contact Number are stored in the school register. There are two major types of databases – Relational and Non-relational. Relational databases are the most commonly used databases today. The following are several types of databases in use: - Flat file databases – Stores data in permanent files that mostly are in text form; Hierarchical databases – Arranges data in a tree-like structure; Network database – Arranges data in network-like structure; Relational databases – Contains a set of tables in which data are related; Object databases – Represents information in the form of objects as used in object-oriented programming. 1.2 FILE SYSTEM Now you must have noticed that maintaining such types of records manually does not allow you to correct, modify or delete the data in the register. Also, searching the details of the student is difficult. Page 5 of 200 To overcome the hassles faced in manual record keeping, this data can be stored in a computer. The student details are stored in the computer in the form of a separate file. In computers, any contents are stored in the form of a file, which is opened and viewed in the respective software. In a computer, a file is a container to store data or information. These files are stored on the storage device of the computer, such as a hard disk drive or pen drive. The student data can be stored in the document file or spreadsheet file. These files stored on the computer can be accessed quickly. To process or manipulate this data, it is required to write the program in computer programming languages. Various operations can be performed through computer programming. It includes searching, sorting, computing the percentage of marks, number of days attendance and retrieving the data. 1.2.1 Limitations of a File System There are certain limitations to maintain and manipulate such types of data when there are several hundreds or thousands of students. It also becomes difficult to maintain the number of files as it increases the volume when data grows. There are certain limitations of the file system to maintain such type of data. The limitations of file system are: Difficulty in Access – Files themselves do not provide any mechanism to retrieve data. Data maintained in a file system is accessed through application programs. While writing such programs, the developer may not anticipate all the possible ways in which data may be accessed. So, sometimes it is difficult to access data in the required format and one has to write an application program to access data. Data Redundancy – Redundancy means the same data is duplicated in different files. For example, if we are maintaining student’s data for various purposes then data such as student names are maintained in different files. The common data in all such files are required to be maintained a number of times. This may cause data redundancy which is difficult to avoid in a file system. Redundancy leads to excess storage use and may cause data inconsistency also. Data Inconsistency – Data inconsistency occurs when the same data maintained in different places do not match. If the student wants to change the spelling of their name, it needs to be changed in the number of files where it appears. Likewise, if a student leaves school, the details need to be deleted from these files. As the files are being maintained by different people, the changes may not happen in one of the files. In that case, the student name will be different (inconsistent) in both the files. Data Isolation – Although these files are maintained for the students of the same class, there is no link or mapping between these files. The school will have to write separate programs to access these files. This is because data mapping is not supported in the file system. In a more complex system where data files are generated by different people at different times, files being created in isolation may be of different formats. In such cases, it is difficult to write new application programs to retrieve data from different files. Data Dependence – Data is stored through specific format or structure in a file. If the structure or format itself is changed, all the existing application programs accessing that file also need to be changed. Otherwise, the programs may not work correctly. This is data dependency. Hence, updating the structure of a data file requires modification in all the application programs accessing that file. Controlled Data Sharing – There can be different categories of users like teachers, office staff and parents. Ideally, not every user should be able to access all the data. It means different types of users should be given different types of access, such as read only. It is very difficult to enforce this kind of access control in a file system while accessing files through application programs. 1.3 DATABASE MANAGEMENT SYSTEM Limitations faced in file systems can be overcome by storing the data in a database where data are logically related. The Database Management Systems (DBMSs) are used as an interface to manage databases. Page 6 of 200 A database is an organised collection of data, generally stored and accessed electronically from a computer system. It supports the storage and manipulation of data. In other words, databases are used by an organisation as a method of storing, managing and retrieving information. It is possible to store and organise related data in a database so that it can be managed in an efficient and easy way. A DBMS is a collection of software components designed to create and maintain databases and control all access to them. It used to create a database, store, manage, update/modify and retrieve data from that database by users or application programs. It is used to provide an effective method of performing database operations, troubleshooting database issues, and restricting data access. Relational Database Management System (RDBMS), which is still popular today, is an advanced version of a DBMS system. Dr. E. F. Codd defined the criteria to determine whether a DBMS is a relational database management system or not. These criteria are known as Codd’s twelve rules. (E. F. Codd, 1985). Some examples of open source and commercial DBMS include MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB etc. as presented in Table 1.1. Table 1.1 Popular DBMS DBMS Primary Database Model Licence Oracle RDBMS Commercial (restricted free version is available) MySQL RDBMS Open Source Microsoft SQL Server RDBMS Commercial (restricted free version is available) PostgreSQL RDBMS Open Source MangoDB Document store Open Source Fig: 1.1 Different types of DBMS/RDBMS available in market Some database management systems include a graphical user interface for users to create and manage databases. Other database systems use a command line interface that requires users to use programming commands to create and manage databases. A database management system hides certain details about how data are actually stored and maintained. Thus, it provides users with an abstract view of the data. A database system has a set of programs through which users or other programs can access, modify and retrieve the stored data. The DBMS serves as an interface between the database and end users or application programs. Retrieving data from a database through a special type of commands is called Querying the database. In addition, users can modify the structure of the database itself through a DBMS. Databases are widely used in various fields. Some applications are given in Table 1.2. Page 7 of 200 Table 1.2 Use of Database in Real-life Applications Application Database to maintain data about Banking Customer information, account details, loan details, transaction details etc. Crop Loan Kisan credit card data, farmer’s personal data, land area and cultivation data, loan history, repayment data etc. Inventory Management of product details, customer information, order details, delivery data etc. Organisation Resource Management Employee records, salary details, information, branch locations etc. Online Shopping Items description, user login details, users preferences, details etc. 1.3.1 Limitations of DBMS Increased Complexity – Use of DBMS increases the complexity of maintaining functionalities like security, consistency, sharing and integrity. Increased data vulnerability – As data are stored centrally, it increases the chances of loss of data due to any failure of hardware or software. It can bring all operations to a halt for all the users. 1.3.2 Application of the DBMS following are important applications of the DBMS: Student Admission System, School Examination System, Library Management System Payroll, HR, Sales and Personnel Management System Accounting System, Hotel Reservation System and Airline Reservation System It is used in the Banking system for Customer information, account activities, Payments, deposits, loans etc. Insurance management system. DBMS is also used by universities to keep all records. Financial purposes like storing information about, sales, and purchases of financial instruments like stocks and bonds. 1.3.2 Advantages of DBMS The advantages of DBMS are: It offers a variety of techniques to store and retrieve data. Uniform administration procedures for data storage and retrieval. Application programmers are never exposed to details of data representation and Storages. A DBMS uses various powerful functions to store and retrieve data efficiently. Offers Data independence, Data Integrity and Data Security and reduces data redundancy. The DBMS implies integrity constraints to get a high level of protection against prohibited access to data. Reduced application development Time and occupies less space. 1.3.4 Disadvantages of the DBMS The disadvantages of DBMS are: Page 8 of 200  Cost of Hardware and Software of a DBMS is quite high, which increases the budget of your organisation. Most database management systems are often complex systems, so the training for users to use the DBMS is required. The use of the same program at a time by many users sometimes lead to the loss of some data. DBMS can't perform sophisticated calculations. Data-sets begin to grow large as it provides a more predictable query response time. It required a processor with a high speed of data processing. The database can fail because of power failure or the whole system stops. The cost of DBMS is dependent on the environment, function, or recurrent annual maintenance cost. 1.3.5 Comparison of Database Management System (DBMS) with File System The comparative points of DBMS with File System are given in Table 1.3. Table 1.3: Comparison of DBMS with File System File System DBMS A file system is a software that manages and DBMS or Database Management System is a organises the files in a storage medium. It software application. It is used for accessing, controls how data is stored and retrieved. creating, and managing databases. The file system provides the details of data DBMS gives an abstract view of data that hides representation and storage of data. the details. Storing and retrieving of data can't be done DBMS is efficient to use as there are a wide efficiently in a file system. variety of methods to store and retrieve data. It does not offer data recovery processes. There is a backup recovery for data in DBMS. The file system doesn't have a crash recovery DBMS provides a crash recovery mechanism mechanism. Protecting a file system is very difficult. DBMS offers a good protection mechanism. In a file management system, the redundancy of The redundancy of data is low in the DBMS data is greater. system. Data inconsistency is higher in the file system. Data inconsistency is low in a database management system. The file system offers lesser security. Database Management System offers high security. File System allows you to store the data as Database Management System stores data as isolated data files and entities. well as defined constraints and interrelation. It does not provide support for complicated Easy to implement complicated transactions. transactions. The centralization process is hard in the File Centralization is easy to achieve in the DBMS Management System. system. It doesn't offer backup and recovery of data if it DBMS systems provide backup and recovery of is lost. data even if it is lost. There is no efficient query processing in the file You can easily query data in a database using Page 9 of 200  system. the SQL language. This system doesn't offer concurrency. DBMS system provides a concurrency facility. 1.4 Key Concepts in DBMS It is important to understand the following concepts to efficiently manage data using a DBMS. (A) Database schema A database schema is a set of schemas for a database's relations. It consists of tables with all attributes with their data types and constraints if any. It also represents the relationships among the tables. It is used to visualise the logical architecture of a database and how the data are organised in a database. The schema of a relation may not change, but the relation, which is a variable, changes over time. Fig: 1.2 Database schema for STUDENTATTENDANCE system (B) Data Constraint Sometimes, it is required to put certain restrictions or limitations on the type of data to be inserted in the columns of a table. This is done by specifying constraints on that column(s) while creating the tables. For example, the constraint that the column mobile number can only have non-negative integer values of exactly 10 digits. Since each student shall have one unique roll number, we can put the NOT NULL and UNIQUE constraints on the Roll Number column. Constraints are used to ensure accuracy and reliability of data in the database (C) Meta-data or Data Dictionary The database schema along with various constraints on the data is stored by DBMS in a database catalogue or dictionary, called meta-data. A meta-data is data about the data. (D) Database Instance When we define a database structure or schema, the state of the database is empty. After loading data, the state or snapshot of the database at any given time is the database instance. We may then retrieve data through queries or manipulate data through updating, modification or deletion. Thus, the state of the database can change, a database schema can have many instances at different times. (E) Query A query is a request to a database for obtaining information in a desired way. Query can be made to get data from one table or from a combination of tables. For example, “find names of all those students present today” is a query to the database. To retrieve or manipulate data, the user needs to write a query using a query language called Structured Query Language (SQL). (F) Data Manipulation Modification of database consists of three operations viz. Insertion, Deletion or Updating. Suppose Rivaan joins as a new student in the class then the student details need to be added in StudentRecord as well as in ParentRecord files of the STUDENTATTENDANCE database. This is Page 10 of 200 called an Insertion operation on the database. In case a student leaves the school, then student as well as parent data need to be removed from StudentRecord, ParentRecord and AttendanceRecord tables, respectively. This is called Deletion operation on the database. Suppose Rivaan’s Parent has changed his mobile number, his Par_Phone should be updated in the ParentRecord file. This is called an Update operation on the database. (G) Database Engine Database engine is the underlying component or set of programs used by a DBMS to create a database and handle various queries for data retrieval and manipulation. 1.5 RELATIONAL DATA MODEL A data model describes the structure of the database and represents data. It defines and represents relationships among relations. In database design, first the conceptual data model is designed for non-technical users. Then, based on the conceptual data models, the logical data models are designed by the technical users. It represents how to store and retrieve data logically. Finally, the logical design models are converted into physical data models that show all table structures. Relational data models are the most commonly used data model. So, in this topic we will focus on relational data models. 1.5.1 Key terms in Relational Data Model In relational data models, tables are called relations that store data for different entities. Each relation in a relational model represents a specific type of entity. An entity is an object and we store data about the object. In other words, a relation is a two-dimensional table used to store data. Let us consider the relational database SCHOOLRECORD along with the three relations (tables) StudentRecord, AttendanceRecord and ParentRecord, as shown in Figure 1.3. Fig. 1.3 Representation SCHOOLRECORD database using Relational Data Model Assume that, a relation AttendanceRecord has attribute Stu_RollNo which links it with corresponding student record in relation StudentRecord. Similarly, attribute Par_ID is placed with the StudentRecord table for extracting parent details of a particular student. If linking attributes are not there in appropriate relations, it will not be possible to keep the database in the correct state and retrieve valid information from the database. Table 1.4 Relation schema along with its description of Student Attendance database Relation Schema Description of attributes StudentRecord Stu_RollNo : unique id of the student (Stu_RollNo, Stu_FName : First name of the student Stu_FName, Stu_LName : Last name of the student Stu_LName, Stu_DOB, Stu_DOB : Student’s date of birth Stu_Address, Stu_Address : Home address of the student Par_ID ) Par_ID : unique id of the parent of the student Page 11 of 200  AttendanceRecord Att_Date : date on which attendance is taken (Att_Date, Stu_RollNo : roll number of the student Att_Status : Either P (for present) or A (for absent) Stu_RollNo, Note: Combination of Att_Date and Stu_RollNo will be Att_Status) unique in each record of the table ParentRecord Par_ID : unique id of the parent (Par_ID, Par_Name : Name of the parent Par_Phone : Contact number of the parent Par_Name, Par_Address : Address of the parent Par_Phone, Par_Email : Email id of the parent Par_Address, Par_Email) Each tuple (row) in a relation (table) corresponds to data of a real-world entity as in StudentRecord, ParentRecord, and AttendanceRecord. In the ParentRecord relation (Table 1.4), each row represents the facts about the parent and each column name in the ParentRecord table is used to interpret the meaning of data stored under that column. A database that is modelled on relational data model concept is called Relational Database. Figure 1.5 shows relation ParentRecord with some populated data. Let us now understand the commonly used terminologies in relational data models using Figure 1.4. Fig 1.4 Relation ParentRecord with its attributes (Columns) and tuples (Rows) Attribute – Characteristic or parameters for which data are to be stored in a relation. Simply stated, the columns of a relation are the attributes which are also referred to as fields. For example, Par_ID, Par_Name, Par_Phone and Par_Address are attributes of relation ParentRecord. Tuple – Each row of data in a relation (table) is called a tuple. In a table with ‘n’ columns, a tuple is a relationship between the ‘n’ related values. Page 12 of 200 Domain – It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify a domain for an attribute. For example, in StudentRecord relation, the attribute Stu_RollNo takes integer values and hence, its domain is a set of integer values. Similarly, the set of character strings constitutes the domain of the attribute Stu_Fname. Degree – The number of attributes in a relation is called the ‘Degree’ of the relation. For example, relation ParentRecord with four attributes is a relation of degree five. Cardinality – The number of tuples in a relation is called the Cardinality of the relation. For example, the cardinality of relation ParentRecord is 10 as there are 10 tuples in the table. 1.5.2 Three Important Properties of a Relation In relational data model, following three properties are observed with respect to a relation which makes a relation different from a data file or a simple table. Property 1: Imposes following rules on an attribute of the relation: a) Each attribute in a relation has a unique name. b) Sequence of attributes in a relation is immaterial. Property 2: Governs following rules on a tuple of a relation: Each tuple in a relation is distinct. For example, data values in no two tuples of relation AttendanceRecord can be identical for all the attributes. Thus, each tuple of a relation must be uniquely identified by its contents. Sequence of tuples in a relation is immaterial. The tuples are not considered to be ordered, even though they appear to be in tabular form. Property 3: Imposes following rules on the state of a relation: All data values in an attribute must be from the same domain (same data type). Each data value associated with an attribute must be atomic (cannot be further divisible into meaningful subparts). For example, Par_Phone of relation ParentRecord has ten digits numbers which are indivisible. No attribute can have many data values in one tuple. For example, any Parent cannot specify multiple contact numbers under the Par_Phone attribute. A special value “NULL” is used to represent values that are unknown or non-applicable to certain attributes. For example, if a parent does not share his or her contact number with the school authorities, then Par_Phone is set to NULL (data unknown). 1.6 KEYS IN A RELATIONAL DATABASE The tuples within a relation must be distinct. It means no two tuples in a table should have the same value for all attributes. That is, there should be at least one attribute in which data are distinct (unique) and not NULL. That way, we can uniquely distinguish each tuple of a relation. So, relational data models impose some restrictions or constraints on the values of the attributes and how the contents of one relation can be referred through another relation. These restrictions are specified at the time of defining the database through different types of keys as given below: 1.6.1 Candidate Key A relation can have one or more attributes that take distinct values. Any of these attributes can be used to uniquely identify the tuples in the relation. Such attributes are called ‘candidate keys’ as each of them are candidates for the primary key. As shown in Figure 1.5, the relation ParentRecord has five attributes out of which Par_ID and Par_Phone always take unique values. No two parents will have the same phone number or same Par_ID. Hence, these two attributes are the candidate keys as they both are candidates for primary key. Page 13 of 200 1.6.2 Primary Key Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation. The remaining attributes in the list of candidate keys are called the ‘alternate keys. In the relation ParentRecord, suppose Par_ID is chosen as primary key, then Par_Phone will be called the alternate key. 1.6.3 Composite Primary Key If no single attribute in a relation is able to uniquely distinguish the tuples, then more than one attribute is taken together as the primary key. Such a primary key consisting of more than one attribute is called ‘Composite Primary key’. In relation to AttendanceRecord, Roll Number cannot be used as primary key as roll number of same students will appear in another row for a different date. Similarly, in relation to AttendanceRecord, ‘Att_Date’ cannot be used as the primary key because the same date is repeated for each roll number. However, combination of these two attributes Stu_RollNo and Att_Date together would always have unique value in the AttendanceRecord table as on any working day, a student would be marked attendance only once. Hence {Stu_RollNo, Att_Date} will combine to make the AttendanceRecord relation composite primary key. 1.6.4 Foreign Key A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a relation (referencing), which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of the referenced relation. The referencing relation is called ‘Foreign Relation’. In some cases, foreign keys can take NULL value if it is not the part of the primary key of the foreign table. The relation in which the referenced primary key is defined is called ‘primary relation’ or ‘master relation’. In Figure 1.5, two foreign keys in STUDENTATTENDANCE database are shown using a schema diagram where the foreign key is displayed as a directed arc (arrow) originating from it and ending at the corresponding attribute of the primary key of the referenced table. The underlined attributes make the primary key of that table. Fig. 1.5 StudentAttendance Database with the Primary and Foreign keys Page 14 of 200  Chapter 2. Structured Query Language (SQL) Once the result date was declared, Shyam was eager to see the result on the website. He opened the website to enter his Roll number to see the result. After entering the Roll number, he pressed the OK button. Immediately the score card of Shyam got displayed on the screen and passed with first division marks. He was very happy and also surprised, how a computer searches the Roll number so fast among approximately 5 lacs students records. Later on, Shyam understood that it was possible because of the database query language which is also known as Structured Query Language (SQL). SQL is used to search, store, modify records in database management systems. In this chapter, you will understand how to create database objects, insert data in the database and various types of commands used to retrieve the required data from the database. Fig. 2.1 Checking result online 2.1 Structured Query Language (SQL) In the file system it is required to write programs to access data. However, in DBMS there exists a Structured Query Language (SQL), which is a special kind of query language used to access and manipulate data from the database. It is the most popular query language used by major relational database management systems (RDBMS), such as MySQL, Oracle, Informix, PostGreSQL, SQL server, MS Access, and Sybase. SQL is easy to learn as the statements comprise descriptive English words. It is possible to interact with a database using SQL very easily. It is simply required to specify what is to be retrieved rather than how to retrieve data from the database. SQL provides statements for defining the structure of data, manipulating data in the database, declaring constraints and retrieving data from the database in various ways, depending on requirement. 2.1.1 Installing MySQL MySQL is an open source RDBMS software which can be easily downloaded from its official website https://dev.mysql.com/downloads. After installing MySQL, start MySQL service. If the screen will so mysql> prompt, then MySQL is ready to accept SQL statements on this prompt. Fig. 2.2: MySQL Shell Following are some important points to be kept in mind while using SQL: Page 15 of 200  SQL is not case insensitive. For example, the column names ‘salary’ and ‘SALARY’ are the same for SQL. SQL statements terminate with a semicolon (;). In multi-line SQL statements, the “;” is not required after the first line. Just press the ‘Enter key’ to continue on the next line. The prompt mysql> then changes to “->”, indicating that statement is continued to the next line. Only at the end of the SQL statement, put “;” and press Enter. 2.2 Data Types and Constraints in MySQL We know that a database consists of one or more relations and each relation (table) is made up of attributes (column). Each attribute has a data type. It is also possible to specify constraints for each attribute of a relation. 2.2.1 Data type of Attribute Data type of an attribute indicates the type of data value that an attribute can have. It also decides the operations that can be performed on the data of that attribute. For example, arithmetic operations can be performed on numeric data but not on character data. Commonly used data types in MySQL are numeric types, date and time types, and string types as shown in Table 2.1. Table 2.1 Commonly used data types in MySQL Data Type Description CHAR (n) Specifies character type data of length ‘n’ where ‘n’ could be any value from 0 to 255. ‘CHAR’ is of fixed length, which means, declaring CHAR (10) implies to reserve spaces for 10 characters. If data does not have 10 characters (for example, ‘city’ has four characters), MySQL fills the remaining 6 characters with spaces padded on the right. VARCHAR (n) Specifies character type data of length ‘n’ where n could be any value from 0 to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, declaring VARCHAR (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of the entered string. So ‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only. INT ‘INT’ specifies an integer value. Each INT value occupies 4 bytes of storage. The range of values allowed in integer type are -2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes. FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4 bytes. DATE The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digits year, MM is the 2 digits month and DD are the 2 digits date. The supported range is '1000-01-01' to '9999-12-31'. 2.2.2 Constraints Constraints are the certain types of restrictions on the data values that an attribute can have. Table 2.2 lists some of the commonly used constraints in SQL. They are used to ensure correctness of data. However, it is not mandatory to define constraints for each attribute of a table. Table 2.2 Commonly used SQL Constraints Constraint Description Ensures that a column cannot have NULL values where NULL means missing/ NOT NULL unknown/not applicable value. UNIQUE Ensures that all the values in a column are distinct/unique. Page 16 of 200 DEFAULT A default value specified for the column if no value is provided. PRIMARY KEY The column which can uniquely identify each row or record in a table. FOREIGN The column which refers to the value of an attribute defined as the primary key in KEY another table. 2.2.3 Types of Structured Query Language (SQL) SQL is a standardised language used for making communication with relational databases and performing various operations on it. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as insert, update and delete data in any database. On the basis of different types of operation, SQL commands are divided into five categories: 1. Data Definition Language (DDL) 2. Data Manipulation Language (DML) 3. Data Query Language (DQL) 4. Transaction Control Language (TCL) 5. Data Control Language (DCL) Fig. 2.3: Types of SQL command 2.3 SQL – Data Definition Language (DDL) It is first necessary to define the relation schema to store data in the database. Defining a schema includes creating a relation and giving name to a relation, identifying the attributes in a relation, deciding upon the datatype for each attribute and also specifying the constraints as per the requirements. Sometimes, it may require making changes to the relation schema also. SQL provides commands for defining the relation schema, modifying relation schema and deleting relations. These are called Data Definition Language (DDL). As you know that the data are stored in relations or tables in a database. Database is a collection of database objects such as tables, queries and views. The CREATE statement is used to create a database and its tables (relations). Before creating a database, it should be clear about the number of tables in the database, the columns (attributes) in each table along with the data type of each column. This is how we decide the relation schema. This category of SQL provides a set of commands to create the database structure or schema. 2.3.1 CREATE Database This SQL command is used to create various database objects. The syntax and example for creating a database is given below. Page 17 of 200 Syntax: CREATE DATABASE databasename; Example 2.1: The following command is used to create a database with the name “SchoolRecord”. mysql> CREATE DATABASE SchoolRecord; After successful execution of the command a message “Query OK” is displayed on the sql prompt. It is also possible to see the newly created database by using the “show” command. The show command displays the newly created database along with some default databases of MySQL as shown in Figure 2.4. Note: In any RDBMS, it is possible to manage multiple databases on a single computer. USE command is used to select the specific database. After selecting the database, it is possible to create tables or querying data from this database. To select the database SchoolRecord, issue the “USE” command followed by the database name. Note: In LINUX OS environment, names for database and tables are case-sensitive whereas in WINDOWS OS, there is no such differentiation. However, as a good practice, it is suggested to write database or table names in the same letter cases that were used at the time of their creation. 2.3.2 CREATE Table After creating database SchoolRecord, it is required to define relations (create tables) in this database. In each relation specify attribute (column name) for each attribute with their required data types. The syntax for the CREATE TABLE statement is as follows. Syntax: CREATE TABLE tablename ( Col_name1 datatype constraint, Col_name2 datatype constraint, : Page 18 of 200  Col_nameN datatype constraint); Let us understand how to choose attribute names and their respected data types. First identify data types of the attributes in table “StudentRecord” along with their constraint, if any. Let us assume that there is a total of 100 students in a class and values of Roll number are in a sequence from 1 to 100. Since the data values of attribute “Stu_RollNo” is stored in digits, the data type integer (INT) is appropriate for this attribute. In the same way, the total number of characters in student First name and Last name can be up to 20 characters. Since the number of characters can vary for different students, the data type VARCHAR is used for these columns. The data type VARCHAR is used for student addresses up to 50 characters in length. The specific data type DATE is used for specifying any type of date. So, DATE data type is used for attribute “Date of Birth”. For student's parent ID, Aadhaar number is used which is a 12-digit number. Since the Aadhaar number is of fixed length and it is not required to perform any mathematical operation, the character data type with fixed length of 12-character, CHAR (12) is used for this attribute. Table 2.3 Data types and constraints for the attributes of relation StudentRecord Attribute Data expected to be stored Data type Constraint Stu_RollNo Numeric value consisting of maximum 3 digits Int Primary Key Stu_FName Variable length string of maximum 20 characters Varchar (20) Not Null Stu_LName Variable length string of maximum 20 characters Varchar (20) Not Null Stu_DOB Date value Date Not Null Stu_Address Variable length string of maximum 50 characters Varchar (50) Not Null Fixed length string of 12 digits for Aadhaar Par_ID Char (12) Foreign Key Number Table 2.4 Data types and constraints for the attributes of relation ParentRecord Attribute Data expected to be stored Data type Constraint Par_ID Fixed length string of 12 digits Aadhaar number Char (12) Primary Key Par_Name Variable length string of maximum 20 characters Varchar (20) Not Null Par_Phone Numeric value consisting of 10 digits Char (10) Null Unique Par_Address Variable length string of size 30 characters Varchar (30) Not Null Par_Email Variable length string of size 30 characters Varchar (30) Table 2.5 Data types and constraints for the attributes of relation AttendanceRecord Attribute Data expected to be stored Data type Constraint Att_Date Date value Date Primary Key* Primary Key* Stu_RollNo Numeric value consisting of maximum 3 digits Int Foreign Key Att_Status ‘P’ for present and ‘A’ for absent Char(1) Not Null Table 2.3, 2.4 and 2.5 show the chosen data type and constraint for each attribute of the relations StudentRecord, ParentRecord and AttendanceRecord respectively. Example 2.2: The following command is used to create table StudentRecord. To create the table in the SchoolRecord database, first open the database with USE SchoolRecord command. Then create the table under the StudentRecord database by using the CREATE TABLE command. Page 19 of 200 Note: “,” is used to separate two attributes and each statement terminates with a semicolon (;). The arrow (->) is an interactive continuation prompt. If we enter an unfinished statement, the SQL shell will wait for us to enter the rest of the statement. Example 2.3: The following command is used to Create table ParentRecord. Example 2.4: The following command is used to Create table AttendanceRecord. 2.3.3 DESCRIBE Table DESCRIBE or DESC command is used to view the structure of an already created table. Syntax: DESCRIBE tablename; Example 2.5: The following SQL command is used to show the structure of the StudentRecord table. Page 20 of 200 The SHOW TABLES statement is used to display all the tables in the database. We have created three tables in the database SchoolRecord. Example 2.6: The following SQL command is used to display the tables created in the database SchoolRecord. It shows all the three tables created so far. 2.3.4 ALTER Table After creating a table, it is possible to add or remove an attribute or modify the datatype of existing attribute or to add constraint in attribute. ALTER statement is used to change or alter the structure of the table. Syntax: ALTER TABLE tablename Add/Modify/Drop attribute1, attribute2,.. (a) Add primary key to a relation Example 2.7: The following SQL command is used to add a primary key to the relation “ParentRecord” A composite primary key is made up of two attributes. The primary key to the “AttendanceRecord” relation will be the composite primary key of two attributes. “AttendanceDate” and “Stu_RollNo”. Example 2.8: The following SQL command is used to add the composite primary key to the relation “AttendanceRecord”. (b) Add foreign key to a relation It is also possible to add foreign keys to the relation, if any. A relation may have multiple foreign keys and each foreign key is defined on a single attribute. Note the following points while adding foreign keys to a relation. The referenced relation must be already created. The referenced attribute must be a part of the primary key of the referenced relation. Data types and size of referenced and referencing attributes must be the same. Page 21 of 200 Syntax: ALTER TABLE table_name ADD FOREIGN KEY (attribute name) REFERENCES referenced_table_name (attribute name); Let us now add foreign key to the table StudentRecord. In table “StudentRecord”, the attribute Par_ID (the referencing attribute) is a foreign key and it refers to attribute Par_ID (the referenced attribute) of table “ParentRecord”. Hence, “StudentRecord” is the referencing table and “ParentRecord” is the referenced table. Example 2.9: The following SQL command is used to add the foreign key. The ALTER statement changes the table StudentRecord. (c) Add constraint unique to an existing attribute In the “ParentRecord” table, attribute “Par_Phone” has a constraint UNIQUE, meaning no two values in that column should be the same. Syntax: ALTER TABLE table_name ADD UNIQUE (attributename); Example 2.10: The following SQL command is used to add the constraint UNIQUE with attribute “Par_Phone” of the table “ParentRecord”. (d) Add an attribute to an existing table Sometimes, it is required to add an additional attribute in a table. The syntax for this is. Syntax: ALTER TABLE table_name ADD attribute_name DATATYPE; Suppose the Principal of the school has decided to award scholarships to some needy students for which the income of the parents must be known. But the school has not maintained an income attribute with table “ParentRecord” so far. Example 2.11: The following command is used to add a new attribute income of data type INT in the table “ParentRecord”. Page 22 of 200 The newly added attribute “income” with data type INT in the table “ParentRecord” can be viewed using the DESC command as follows. (e) Modify datatype of an attribute It is possible to modify the data types of the existing attributes of a table using the following statement. Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE; Suppose, to change the size of attribute “Par_Address” from VARCHAR (30) to VARCHAR (40) of the “ParentRecord” table. Example 2.12: The following command is used to change the size of attribute “Par_Address” in the“ParentRecord” table. (f) Modify constraint of an attribute When creating a table, by default each attribute takes null value except for the attribute defined as primary key. It is possible to change an attribute’s constraint from NULL to NOT NULL using the ALTER statement. Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL; Note: It is required to specify the data type of the attribute along with constraint NOT NULL while using MODIFY. Example 2.13: The following command is used to associate NOT NULL constraint with attribute “Stu_FName” of table “StudentRecord”. (g) Add default value to an attribute The syntax to specify the default value for an attribute is, Page 23 of 200 Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value; To set the default value of “Stu_DOB” of “StudentRecord” to 15th May 2000, write the following statement. Note: It is required to specify the data type of the attribute along with DEFAULT while using MODIFY. (h) Remove an attribute It is possible to remove attributes from a table using ALTER. Syntax: ALTER TABLE table_name DROP attribute; Example 2.14: The following command is used to remove the attribute income from the table “ParentRecord”. (i) Remove primary key from the table Sometimes it may be required to remove the primary key constraint from the table. In such a case, the syntax for ALTER TABLE command is. Syntax: ALTER TABLE table_name DROP PRIMARY KEY; Example 2.15: The following command is used to remove primary key of table “ParentRecord” Note: The primary key is dropped from the StudentRecord table, but each table should have a primary key to maintain uniqueness. Hence, to use ADD command to specify primary key for the StudentRecord table as shown in earlier examples 2.3.5 DROP TABLE Command Sometimes it may require removing a table in a database or the database itself. DROP statement is used to remove a database or a table permanently from the system. Since this command will delete the table or database permanently, you have to be cautious while using this statement as it cannot be undone. Let us assume that you have created a table with the name “ParantRecord” instead of “ParentRecord”. DROP command can be used to delete the table created with the wrong name. Page 24 of 200 Syntax: DROP TABLE table_name; It is also possible to drop the entire database. Syntax: DROP DATABASE database_name; Example 2.16: The following command is used to delete the table name “ParantRecord” from the current database. Cautions: 1. Using the Drop statement to remove a database will ultimately remove all the tables within it. 2. DROP statements will remove the tables or databases created by you. Hence you may apply the DROP statement at the end of the chapter. 2.3.6 TRUNCATE TABLE Command It is possible to remove all records from a table using the TRUNCATE command. Later on, you can insert new records in the same table. This command will delete all records from the table but table structure will exist in the database. While using DROP command, all the records with table structure will be deleted from the database. So, care should be taken while using both TRUNCATE and DROP commands in SQL. Syntax: Truncate Table Table_Name; Example 2.17: The following command is used to Truncate the table “StudentRecordBackup”. Page 25 of 200 2.3.7 CREATE TABLE from Existing Table If you want to create a new table from an existing table with partial or additional fields, then you can use the CREATE table command with the SELECT statement. The new table is created with the result of the SELECT statement with results provided by it. Syntax: mysql> Create table NewTableName AS (Select Field 1, Field 2, Field 3, …Field N from Old_Table_Name) Example 2.18: The following command is used to Create table “NewStudentRecord” from the existing table “StudentRecord”. It will create a new table named “NewStudentRecord” with only 5 attributes and all the records which were inserted in this table earlier. Page 26 of 200 It is possible to create a new table with all attributes and all records available in the existing table. Example 2.19: The following command is used to create a new table “StudentRecord1” with all attributes and all records available in the existing table “StudentRecord”. 2.3.8 RENAME TABLE command Sometimes it may be required to change the name of an existing table. It is possible to do so by using the RENAME or ALTER command. Syntax: RENAME TABLE old_table_name TO new_table_name; Example 2.20: The following command is used to rename the table “NewStudentRecord” to “StudentRecord1” mysql> RENAME TABLE NewStudentRecord TO StudentRecord1; It is possible to rename multiple tables using a single command as under. Syntax: RENAME TABLE Old_tableA TO New_tableA, Old_tableB TO New_tableB, Old_tableC TO New_tableC; It is also possible to use the ALTER command to rename the table as given below. Syntax: ALTER TABLE Old_table_name RENAME TO new_table_name; Example 2.21: The following is the command to alter the table “StudentRecord1” to “StudentRecord2” mysql> ALTER TABLE StudentRecord1 RENAME TO StudentRecord2; Page 27 of 200 2.3.9 CREATE VIEW command Like table, view is another database object. It is a special kind of virtual table. It does not hold its own data. A view can have rows and columns just like in a table. It is possible to create a view using the CREATE VIEW command, by selecting fields from one or more tables present in the database. A View can either have all the rows of a table (s) or specific rows based on certain criteria. The syntax to create a view is as under. Syntax: CREATE VIEW view_name AS SELECT column1, column2.... columnN FROM table_name WHERE condition; Example 2.22: The following is the command to create a view from a single table. Now the view named EMP_VIEW will be created with only those employee records who have a salary more than 10000. You can use this view similar to the Employee table to see all records using the SELECT command. To see all records from EMP_VEW, use the SELECT command as under. Practical Activity 2.1 – Create the table “Employee” and “Department” in MySQL with the following attributes specification: Employee Table Attribute Data expected to be stored Data type Constraint empno Numeric value consisting of 4 digits Int Primary Key ename Variable length string of max 30 characters Varchar (30) Not Null job Variable length string of max 15 characters Varchar (15) Not Null mgr Numeric value consisting of 4 digits Int Not Null hiredate Date of joining the company Date Not Null sal Numeric value consisting of 6 digits Int Not Null Page 28 of 200 comm Numeric value consisting of 4 digits Int Not Null Dept no which is Numeric type consisting of Deptno Int maximum 2 digits Department Table Attribute Data expected to be stored Data type Constraint deptno Numeric value consisting of 4 digits Int Primary Key dname Variant length string of max 20 characters Varchar (20) Not Null loc Variant length string of max 25 characters Varchar (25) Not Null 2.4 SQL FOR DATA MANIPULATION LANGUAGE (DML) In the previous session, we created the database SchoolRecord with three relations (or tables) i.e. StudentRecord, ParentRecord and AttendanceRecord. Creating a table, creates its structure only. It is required to manipulate the data in the table by entering, deleting and updating the data records. The commands or statements used to insert, delete and update the records come under SQL Data Manipulation Language (DML). Data manipulation means either retrieval (access) of existing data, insertion of new data, removal of existing data or modification of existing data in the database. Updation and deletion of data records are also important in SQL. These data manipulation methods are discussed in the following section. 2.4.1 INSERTION of Records INSERT INTO statement is used to insert new records in any table or relation. Syntax: INSERT INTO tablename VALUES (value 1, value 2,....); Here, value 1 corresponds to attribute 1, value 2 corresponds to attribute 2 and so on. It is required to specify attribute names in the INSERT statement if there are exactly the same number of values in the INSERT statement as the total number of attributes in the table. Caution: While populating records in a table with foreign key, ensure that records in referenced tables are already populated. Let us insert some records in the SchoolRecord database. First insert the records in the ParentRecord table first as it does not have any foreign key. A set of sample records for the ParentRecord table is shown in Table 2.6. Table 2.6 Records to be inserted into the ParentRecord Table Par_ID Par_Nam Par_Phone Par_Address Par_Email e 452695874564 Manu P 9834567890 203, Khandari, Agra, [email protected] Singh UP 252154687451 Ashok K 9845678910 144 Gr Kailash, New [email protected] Sharma Delhi 362115264625 Ashutos 9856789120 JP Greens, Noida, UP [email protected] h Gaur 602125125261 Sachin 9812389120 Kanda, Bagheshwar, [email protected] Agrawal UK 225423344657 Chandra 9891201238 Fortune Somya, [email protected] Roy Bhopal, MP 268953264578 Dinesh Lajpat Nagar, [email protected] Dixit Mathura, UP Page 29 of 200  485466192343 Rizwan 9255614563 Deep Nagar, Sahrsa, [email protected] Alam Bihar 521556651761 Ashish 8544556978 T Nagar, Hyderabad, [email protected] Gupta Telangana m 686113652987 Gurmeet 9635214789 Shahid Nagar, [email protected] Singh Amritsar, PB 954891122475 Michal 8554658958 Guindy, Chennai, TN [email protected] DeSousa Example 2.23: The following command is used to insert the record in the “ParentRecord” table. We can use the SQL statement “SELECT * from table_ name;” to view the inserted record after any statement to see the current changes in table. It is also possible to provide values only for some of the attributes in a table by just specifying the attribute name alongside each data value as per the following syntax. Syntax: INSERT INTO tablename (column1, column2,...) VALUES (value1, value2,...); Suppose to insert the sixth record in the “ParentRecord” table (Table 2.6) keeping the value of “Par_Phone” to NULL. Then it is required to insert the values for the other four fields. In this case, specify the names of attributes in which the values are to be inserted. The values must be given in the same order in which attributes are written in the INSERT command. Example 2.24: The following command is used to insert the record in the “ParentRecord” table by specifying the field name and corresponding values. Now observe that all the four values have been inserted in the table ParentRecord except “Par_Phone” which is being set to NULL at the time of creating a table. Page 30 of 200 Note: Text and date values must be enclosed in ‘ ’ (single quotes). Practical Activity 2.2 – Insert the records in the ParentRecord table using INSERT command and check the records inserted in ParentRecord as below. Practical Activity 2.3 – Insert the records in StudentRecord table (Table 2.7). Table 2.7 Records to be inserted into the StudentRecord table Stu_ Stu_FName Stu_LName Stu_DOB Stu_Address Par_ID RollNo 1 Rajvardhan Singh 5/15/2003 203, Khandari, Agra UP 452695874564 2 Trilok Sharma 8/15/2004 144 Gr Kailash, New Delhi 252154687451 3 Aditi Gaur 4/6/2005 JP Greens, Noida, UP 362115264625 4 Anshika Agrawal 5/17/2003 Kanda, Bagheshwar, UK 602125125261 5 Nandini Roy 12/29/2003 Fortune Somya, Bhopal, MP 225423344657 6 Pawani Dixit 11/12/2004 Lajpat Nagar, Mathura, UP 268953264578 7 Hiba Rizwan 12/3/2006 Deep Nagar, Sahrsa, Bihar 485466192343 8 Riddhi Gupta 1/11/2005 T Nagar, Hyderabad, Telangana 521556651761 9 Manpreet Singh 9/8/2005 Shahid Nagar, Amritsar, Punjab 686113652987 10 John DeSousa 8/17/2005 Guindy, Chennai, TN 954891122475 Example 2.25: The following command is used to insert the first record in table “StudentRecord”. Page 31 of 200 When column names are not mentioned in the INSERT command, then it is necessary to mention the values for all the columns. So, if there is no “ParentID” for Trilok, then mention the NULL value for the “Par_ID”. Example 2.26: The following command inserts the second record with “Par_ID” value as NULL. mysql>INSERT INTO StudentRecord VALUES (2,'Trilok','Sharma','8/15/2004', '144 Gr Kailash','New Delhi' NULL); Note/Tip: Please be careful while entering the date in the INSERT command. Use the ‘YYYY-MM-DD’ format to write dates. Practical Activity 2.3.1 – Use INSERT command Insert the records in the employee table using the INSERT command and display it after inserting all records using the SELECT statement. Insert the records in the Department table using INSERT command and display it after inserting all record using SELECT statement. Page 32 of 200 2.4.2 UPDATION of Records using UPDATE and DELETE Command UPDATE and DELETE are also part of SQL Data Manipulation Language (DML). UPDATE command is used to make changes in existing data value(s) of one or more columns of existing records in a table. For example, we may require some changes in address, phone number or spelling of name. Syntax: UPDATE table_name SET attribute1 = value1, attribute2 = value2, … WHERE condition; In the ParentRecord table, Phone number is not available for Parent Name Dinesh Dixit. So, it is required to update Phone number of Dinesh Dixit, update the table ParentRecord and use the command. Example 2.27: The following command is used to update the Phone number of Dinesh Dixit in the ParentRecord table. The updated data can be verified using the statement. SELECT * FROM ParentRecord. It is also possible to update values for more than one column using the UPDATE statement. Suppose, the ParentRecord with Par_ID 485466192343 has requested to change Address to 'WZ - 68, Azad Avenue, Boriwali, Mumbai’ and Phone number to '9988776644'. Example 2.28: The following SQL statement will update this record. mysql> UPDATE ParentRecord SET Par_Address = 'WZ - 68, Azad Avenue, Boriwali, Mumbai’, Par_Phone = 9988776644 WHERE Par_ID = 485466192343; The changes affected can be verified by using the SELECT statement as below. Page 33 of 200 2.4.3 DELETION of Records using DELETE DELETE statement is used to delete or remove one or more records from a table. Syntax: DELETE FROM table_name WHERE condition; Suppose the student with roll number 2 has left the school. Example 2.29: The following SQL statement is used to delete that record from the STUDENT table. The changes affected can be verified by using the SELECT statement as below. Caution: The WHERE clause should be used in the UPDATE and DELETE statement, otherwise it will apply on all the records. 2.5 SQL FOR DATA QUERY LANGUAGE (DQL) So far, we have learned to create a database, store and manipulate data in the database tables. The data stored in a database can be retrieved using a mechanism called Query. SQL provides efficient mechanisms to retrieve data stored in multiple tables in a MySQL database (or any other RDBMS). The SQL statement SELECT is used to retrieve data from the tables in a database and is also called a query statement. One of the most commonly used DQL is the SELECT statement. Page 34 of 200 2.5.1 SELECT Statement In SQL, the SELECT statement is used to retrieve data from tables in a database and output is displayed as per the specified parameter on successful execution of the statement. Syntax: SELECT attribute1, attribute2,... attribute N FROM table_name WHERE condition; Here, attribute1, attribute2,... attributeN are the names of columns of the table table_name from which data is to be retrieved. The FROM clause is always written with the SELECT clause as it specifies the name of the table from which data has to be retrieved. The WHERE clause is optional and is used to retrieve data to meet any specified condition(s). To select all the columns and rows available in a table, use the following select statement. SELECT * FROM table_name; Here * is used to retrieve all columns/attributes available in the table. Let us use a SELECT statement to retrieve names of the students whose names start with the alphabet “D”. Example 2.30: The following SQL query statement is used to retrieve the name and date of birth of a student whose roll number is 1. In the above query, observe that the Student Roll Number and Date of birth of the student whose roll number is 1 is retrieved using WHERE clause. 2.5.2 Querying using database OFFICE Let us consider an EMP table of employee databases with the following fields. The “empno” is a primary key and “deptno” as foreign key. Table 2.8 shows the data entered in the Emp table. Table 2.8 Records available in EMP table empno ename job mgr hiredate sal comm deptno 7019 Smita Clerk 7552 12/14/1994 8800 NULL 20 7049 Alam Salesman 7348 02/17/1995 9600 1800 30 7171 Wasim Salesman 7348 02/19/1995 9250 2000 30 7216 Jawahar Manager 7489 03/30/1995 10975 NULL 20 7304 Manoj Salesman 7348 09/25/1995 9250 2900 30 7348 Balwinder Manager 7489 04/28/1995 10850 NULL 30 7432 Chetana Manager 7489 06/06/1995 10450 NULL 10 7438 Sachin Analyst 7216 12/05/1996 11000 NULL 20 Page 35 of 200 7489 Kushaal President NULL 11/14/1995 13000 NULL 10 7494 Tarun Salesman 7348 09/05/1995 9500 0 30 7526 Amar Clerk 7438 01/08/1997 9100 NULL 20 7550 Jyoti Clerk 7348 11/30/1995 8950 NULL 30 7552 Farhan Analyst 7216 10/27/1995 11000 NULL 20 7584 Mohan Clerk 7432 01/20/1996 9300 NULL 10 7984 Lalitha Clerk 7432 05/23/1998 10300 NULL 10 Now if you wish to retrieve the desired data from the table, let us see how to apply the SELECT clause to retrieve the data. (a) Retrieve selected columns – It is possible to retrieve the data of one column of a table. Example 2.31: The following SQL query statement is used to retrieve employee numbers of all employees in the table. Observe that the above query retrieves the empno of all the employees from the Emp table as only one column is specified to retrieve. Let us see another query that selects two columns such as emp no and corresponding employee name. Modify the same query by specifying two fields of table as “empno” and “ename”. and observe the desired output as below. Example 2.32: The following SQL query statement will retrieve the data of employee number and name in two columns. Page 36 of 200 (b) Renaming of columns – There are specific naming conventions of the fields in the table. It is possible to rename any column while displaying the output by using the alias 'AS'. Example 2.33: The following SQL query statement selects Employee name as “Name” in the output for all the employees. Example 2.34: The following SQL query statement will calculate and to display the annual salary of employees. Annual salary is calculated as “sal*12”. Page 37 of 200 Now it doesn’t look nice to display the caption as “sal*12” in the table. It is possible to display it with new caption as “Annual Salary” for “sal*12”. The revised query and its output are given below. Observe that “ename” is shown with the caption as “Name” and “sal*12” is shown with the caption as “Annual Income”. Note – Annual Income is just the caption to display. It will not add as a new column in the database table. It is just for displaying the output of the query. If an aliased column name has space as in the case of Annual Income, it should be enclosed in quotes as ‘Annual Income’. (c) Distinct Clause – The SELECT clause retrieves all the data through query as output. There may be a chance of duplicate values such as two persons with the same name working in the department. The DISTINCT clause has provision to retrieve the unique records by omitting the duplicate records. The DISTINCT clause is used for this purpose. Example 2.35: The following SQL query statement shows the different departments available in the “emp” table. Page 38 of 200 Let us understand how to retrieve different types of jobs available using the DISTINCT clause in the following example. Example 2.36: The following SQL query statement will use a DISTINCT clause to retrieve different types of jobs available in the “emp” table. Observe that there are five different job titles although more records exist. (d) WHERE Clause – It retrieves data that meet some specified conditions. In our OFFICE database, more than one employee can have the same salary. Example 2.37: The following SQL query statement will give distinct salaries of the employees working in the department number 10. Observe in the output that all the records of employees working in dept no. 10 and having the distinct salary are retrieved. In the above example, = operator is used in the WHERE clause. Other relational operators like (=, !=) can also be used to specify conditions as per your requirement. The logical operators AND, OR, and NOT are used to combine multiple conditions. Let us see how to compare columns/fields value/s to specific required records or columns. Page 39 of 200 Example 2.38: The following SQL query statement will display all the details of those employees of 30 departments who earn more than 5000. Note: Observe the output, two different conditions are being tested separately. First condition tested for Salary is greater than 5000 and second condition for department number is 10. AND operator used to join both conditions. Let us make a comparison of salaries like who is getting more than 8000 and less than 11000. Example 2.39: The following SQL query statement will select the name and department number of all those employees who are earning salary between 8000 and 11000 inclusive of both values. The query in example 2.39 defines a range of salary between 8000 and 11000 that can also be achieved using a comparison operator BETWEEN, in the query as below. The output of this query will be the same as above. Page 40 of 200 Note: The BETWEEN operator defines the range of values in which the column value must fall into, to make the condition true. Example 2.40: The following SQL query statement will select details of all the employees who work in any of the department number 10, 20, or 40. (E) Membership operator IN The IN operator compares a value with a set of values and returns as true if the value belongs to that set. The query given in Example 2.40 can be rewritten using the IN operator as below. mysql> SELECT * FROM emp WHERE deptno IN (10, 20, 40); It will give the same output as above. Example 2.41: The following SQL query statement selects details of all the employees except those working in department number 10 or 20. Page 41 of 200 Note: Here NOT operator is used in combination with IN to retrieve all records except with deptno 10 and 20. (F) ORDER BY Clause – It is used to display data in an ordered form with respect to a specified column. By default, ORDER BY displays records in ascending order of the specified column values. The DESC keyword is used to display the records in descending order. Let us arrange the records in ascending or descending order using the ORDER BY clause with DESC clause example 2.42. Example 2.42: The following SQL query statement selects details of all the employees in ascending order of their salaries. Observe that the records are displayed in ascending order of salary of each employee. To arrange records in descending order, use DESC clause with ORDER BY as in example 2.43. Example 2.43: The following SQL query statement selects details of all the employees in descending order of their salaries. Page 42 of 200 Note: DESC clause used after the column name on which the records to be displayed in descending order. (G) Handling NULL Values – SQL supports a special value called NULL to represent a missing or unknown value. For example, the “Par_Phone” column in the table “ParentRecord” can have missing values for certain records. Hence, NULL is used to represent such unknown values. It is important to note that NULL is different from value 0 (zero). Also, any arithmetic operation performed with NULL value gives NULL. For example, 5 + NULL = NULL because NULL is unknown hence the result is also unknown. In order to check for NULL value in a column, use IS NULL operator in particular statements. Example 2.44 illustrates the use of NULL clauses. Example 2.44: The following SQL query statement selects details of all employees who have not been given a bonus. This implies that the bonus column will be blank. Observe the output and see column mgr and comm where NULL is present. Page 43 of 200 It is also possible to join a NULL statement with any other condition. Example 3.11 shows how to use it in a statement. Example 2.45: The following SQL query statement selects emp number, employee names and job of all those employees who have been given a comm (i.e., comm is not null) and works in the department 30. (H) Having clause – It is used in a SELECT statement to make a group with certain conditions as a result of query. Syntax: SELECT expression1, expression2,... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2,... expression_n HAVING condition; Example 2.45 shows how to use Group by and Having clauses jointly. The HAVING clause must follow the GROUP BY clause in any SELECT query and must also be preceded by ORDER BY clause if used. Example 2.45: The following SQL query statement selects jobs, number of employees in that job, their total salary and department number wise list where minimum 3 employees of the same type of job are working. Page 44 of 200 (I) Substring pattern matching – Many times it may require that the query should not retrieve that exact text or value, rather it should retrieve the matching of a few characters or values. For example, to find names starting with “M” or to find pin codes starting with “11”, is called substring pattern matching. Such patterns cannot match using = operator. SQL provides a LIKE operator that can be used with the WHERE clause to search for specific patterns in a column. The LIKE operator makes use of the following two wild card characters - (%) and (-). The percent (%) is used to represent zero, one, or multiple characters. The underscore (_) is used to represent exactly a single character. There are several situations when we search data records for some pattern matching. A very common situation when you search for any contacts in your smartphone, you just start typing the first few characters of the name, then immediately a list appears with these characters and you tap on the required name to call. Example 3.46 to 3.51 demonstrates such situations to search some patterns in text values of records using LIKE clauses. Example 2.46: The following SQL query statement selects details of all those employees whose name starts with 'K'. Example 2.47: The following SQL query statement selects details of all those employees whose name ends with 'a', and receives a salary more than 8500. Example 2.48: The following SQL query statement selects details of all those employees whose name consists of exactly five letters and starts with any letter but has ‘mita’ after that. You can also match a particular character or string in between the text simply by using wildcard characters as shown in example 2.49. Page 45 of 200 Example 2.49: The following SQL query statement selects all columns of all employees containing 'ma' as a substring in name. Example 2.50: The following SQL query statement selects all columns of employees containing 'a' as the second character in their names. Example 2.51: The following SQL query statement selects records of all the employees except Alam. 2.6 SQL FOR DATA CONTROL LANGUAGE (DCL) Data Control Language (DCL) is the part of SQL, which has commands to manage users for their work permission. The user will be able to work as per the permissions granted to them by DBA (Database Administrator). DCL includes the commands GRANT and REVOKE, which are used to provide rights and permissions to users. Page 46 of 200 GRANT statement – The GRANT statement is used to give access privileges to a specific user to work with any selected database only. Syntax: GRANT SELECT, UPDATE ON Test_Table TO NewUser1, NewUser2; Example: GRANT SELECT, UPDATE, DELETE ON carshowroom TO 'WebUser'; Here the user 'WebUser' will be able to use only three SELECT, UPDATE and DELETE SQL statements when working on carshowroom database. REVOKE statement – The REVOKE statement is used to withdraw privileges from a specific user so that specific user could not use a specific statement on a selected database. In other words, it is useful to take back the given permission/s from the user. Syntax: REVOKE Privilege_Name ON Object_Name FROM User_Name. Example: REVOKE DELETE ON carshowroom FROM WebUser; 2.7 SQL FOR TRANSACTION CONTROL LANGUAGE (TCL) Transaction control language (TCL) is the part of SQL commands that allows to permanently change the databases or undo the database transactions. It is similar to saving the database or undoing the current changes. The COMMIT, ROLLBACK and SAVEPOINT statements come under this category. COMMIT – Commit command is used to save all the transactions to the database. After completing any operation or SQL statement, you can simply write COMMIT as the next statement to permanently save data in the database. Syntax: Commit; Example: DELETE FROM ClassStudents WHERE RollNo =25; Commit; Here, after the DELETE statement, the COMMIT statement is used. It means the student record whose RollNo is 25 is permanently deleted. Now after the COMMIT statement, it is not possible to rollback the record of that student. ROLLBACK – ROLLBACK command allows to undo transactions that have not already been saved to the database. This statement is useful to restore the database to the state where the last commit statement was used. Rollback statement is also used with SAVEPOINT to jump to specific Savepoint in the database transactions. Syntax: ROLLBACK; SAVEPOINT – This command helps to set a Savepoint within a transaction. Basically, the SAVEPOINT statement is used to save a transaction temporarily so that the user can rollback to that point as and when required. Syntax: SAVEPOINT Savepoint_Name; Page 47 of 200  CHAPTER 3. FUNCTIONS IN SQL Introduction to Function in SQL There are various readily available functions in SQL that can be used in queries. It includes single row functions, multiple row functions, group records based on some criteria, and working on multiple tables using SQL. A function is used to perform some particular tasks and it returns zero or more values as a result. Functions are useful while writing SQL queries also. Functions can be applied to work on single or multiple records (rows) of a table. 3.1 SQL functions SQL functions are categorised as Single Row functions and Aggregate functions, depending on their application in one or multiple rows. Single Row Functions are also known as Scalar functions. Single row functions are applied on a single value and return a single value. These are used in SELECT, WHERE, and ORDER BY clauses. MATH, STRING and DATE functions are examples of single row functions. Aggregate functions are also called Multiple Row functions. These functions work on a set of records as a whole and return a single value for each column of the records on which the function is applied. These are used with SELECT clauses only. MAX ( ), MIN ( ), AVG ( ), SUM ( ), COUNT ( ) and COUNT (*) are examples of multiple row functions. To demonstrate the use of SQL function, let us create a database called CARSHOWROOM having the schema with four relations as shown in Figure 3.1. Fig 3.1: CARSHOWROOM database schema Inventory – Stores Car id, Car Name, Price, Model, Year of manufacturing and fuel type for each car in inventory of the showroom. Table 3.1: Attribute specification of “Inventory” table Attribute Data expected to be stored Data type Constraint CarID Alpha-Numeric value consisting of Varchar (4) Primary Key maximum 4 digits CarName Variant length string of maximum 20 Varchar (20) Not Null characters Price Numeric value consisting of car price. Int Not Null Model Variable length string of maximum 4 Varchar (10) Not Null characters Page 48 of 200  YearManufacturer Variable length string of maximum 4 Varchar (4) Not Null characters FuelType Variable length string of max 10 characters Varchar (10) Not Null Customer – Stores Customer id, name, address, phone number and email for each customer. Table 3.2: Attribute specification of “Customer” table Attribute Data expected to be stored Data type Constraint Alphanumeric value consisting of characters and CustID Varchar (5) Primary Key digits, max 5 chars CustName Variable length string of max 30 characters Varchar (30) Not Null CustAdd Variable length string of max 50 characters Varchar (50) Not Null Phone Numeric value consisting of 10 digits Char (10) Not Null Email Variable length string of max 50 characters Varchar (20) Not Null Sale – Stores the invoice number, car id, customer id, sale date, mode of payment, sales person’s employee id and selling price of the car sold, Table 3.3: Attribute specification of “Sale” table Attribute Data expected to be stored Data type Constraint InvoiceNo Alpha-Numeric value consisting of Characters Varchar (6) Primary Key and digits, max 6 chars Alpha-Numeric value consisting of maximum 4 CarID Varchar (4) Foreign Key digits Alpha-Numeric value consisting of Characters CustID Varchar (5) Foreign Key and digits, max 5 chars SaleDate Date value Date Not Null PaymentMode Variant length string of max 20 characters Varchar (20) Not Null Alpha-Numeric value consisting of maximum 4 EmpID Varchar (4) Foreign Key chars only SalePrice Car price will be as Numeric Value Int Not Null Employee – Stores employee id, name, date of birth, date of joining, designation and salary of each employee in the showroom. Table 3.4: Attribute specification of “Employee” table Attribute Data expected to be stored Data type Constraint Alpha-Numeric value consisting of maximum 4 EmpID Varchar (4) Primary Key chars only EmpName String of max 20 characters Varchar (20) Not Null DOB Date value Date Not Null DOJ Date value Date Not Null Designation String of max 20 characters Varchar (20) Not Null Salary Numeric value Int Not Null Page 49 of 200 To proceed further, create database CARSHOWROOM and create all four tables as per the above specification. Insert the records in tables Inventory, Customer, Sale and Employee using INSERT command. The records of these four relations can be viewed using the SELECT command. Execute the following query to view the records of the “inventory” table. After successful execution of the query, the records entered in the “inventory” table will be displayed. Execute the following query to view the records of the “customer” table. After successful execution of the query, the records entered in the “customer” table will be displayed. Execute the following query to view the records of the “sale” table. After successful execution of the query, the records entered in the “sale” table will be displayed. Page 50 of 200 Execute the following query to view the records of the “employee” table. After successful execution of the query, the records entered in the “employee” table will be displayed. 3.2 Single Row Functions Figure 3.2 lists different single row functions under three categories — Numeric (Math), String, Date and Time. 1. Math Functions accept numeric values as input and return a numeric value as a result. 2. String Functions accept character value as input and return either character or numeric values as output. 3. Date and Time functions accept date and time value as input and return numeric or string or Date and Time as output. Fig 3.2: Categories of single row functions in SQL 3.2.1 Math Functions Three commonly used numeric functions are POWER (), ROUND () and MOD (). Their usage along with syntax is given below. 1. POWER (X, Y) or POW (X, Y) – calculates X to the power Y Page 51 of 200 2. ROUND (N, D) – Rounds off number N to D number of decimal places. If D=0, then it rounds off the number to the nearest integer. 3. MOD (A, B) – Returns the remainder after dividing number A by number B. Practical Activity 3.1 – Demonstrate to use math function ROUND In order to increase sales, suppose the car dealer offers the customers to pay the total amount in 10 easy EMIs (Equal Monthly Instalments). Assume that EMIs are required in multiples of 10000. For that, the dealer wants to list the CarID and Price along with the following data from the Inventory table. Step 1. Calculate GST as 12 percent of Price and apply ROUND function to it. Execute the query to round off the GST to one decimal place and display the records with the fields CarID, CarName and GST. Step 2. Add a new column “FinalPrice” to the table “inventory”. Update the table “inventory” with “FinalPrice” as the sum of Price and 12 percent of the GST. Apply the ROUND function to round off the GST to one decimal place. Execute the following query to do this. Page 52 of 200 Display the values of “FinalPrice” for all the records by using the SELECT command. Step 3. Calculate and display the amount to be paid each month in multiples of 1000 which is calculated after dividing the FinalPrice of the car into 10 instalments. After dividing the amount into EMIs, find out the remaining amount to be paid immediately, by performing modular division. Use the SELECT command to display the result. Execute the following query to do this. Step 4. Execute the following query to display the “InvoiceNo” and “Commission” value rounded off to zero decimal places. Step 5. Execute the following query to display the details of “sale” table where payment mode is credit card. Page 53 of 200  Step 6. Execute the query to add a new column “Commission” with total length of 7 with 2 decimal places to the “sale” table. Step 7. Execute the query to calculate commission for sales agents as 12% of “SalePrice”. Step 8. Execute the following query to insert the values to the newly added column “Commission” and then display all records of the “sale” table where Commission > 73000. Step 9. Execute the following query to display InvoiceNo, EmpID, SalePrice and Commission such that commission value is rounded off to 0. 3.2.2 String Functions String functions can perform various operations on alphanumeric data which are stored in a table. They can be used to change the case such as uppercase to lowercase or vice-versa, extract a substring, calculate the length of a string and so on. Some of the string functions with examples are given below. Page 54 of 200 1. UCASE (string) OR UPPER (string) – converts string into uppercase. 2. LCASE (string) OR LOWER (string) – converts string into lowercase. 3. MID (string, pos, n) OR SUBSTRING (string, pos, n) OR SUBSTR (string, pos, n) – Returns a substring of size n starting from the specified position (pos) of the string. If n is not specified, it returns the substring from the position pos till end of the string. 4. LENGTH (string) – Return the number of characters in the specified string. Page 55 of 200 5. LEFT (string, N) – Returns N number of characters from the left side of the string. 6. RIGHT (string, N) – Returns N number of characters from the right side of the string. 7. INSTR (string, substring) – Returns the position of the first occurrence of the substring in the given string. Returns 0, if the substring is not present in the string. 8. LTRIM (string) – Returns the given string after removing leading white space characters. 9. RTRIM (string) – Returns the given string after removing trailing white space characters. Page 56 of 200 10. TRIM (string) – Returns the given string after removing both leading and trailing white space characters. Practical Activity 3.2 – Demonstrate to use string function Let us use Customer relation to understand the working of various string functions. Step 1. Execute the followings query to display customer name in lower case and customer email in upper case from “customer” table. Step 2. Execute the following query to display the length of email and part of the email from the email id before the character ‘@’. The function INSTR will return the position of “@” in the email address. So, to print email id without “@” position -1 is used. Let us assume that four-digit area code is reflected in the mobile number starting from position number 3. For example, 1851 is the area code of mobile number 9818511338. Step 3. Execute the following query to display the area code of the customer living in Rohini. Page 57 of 200 Step 4. Execute the following query to display emails after removing the domain name extension “.com” from emails of the customers. Step 5. Execute the following query to display det