. (aaron 5) RDBMS CONCEPTS AND MYSQL Introduction ‘The key to organizational success 1s effective decision making yy ne \d accurate information. Hence plays a critical role today’s competitive environmer Management Software (DBMS) simplifies the task of managing and extracting useful information out of it. In this chapter, we about the basic concepts of databases. Dat BASICS OF DATABASE SYSTEM Database Database : Database is a collection of inter-related data iit composed of a collection of files that are linked in such a may information from one of the files may be combined with from other files so that user may receive the exact needed. For example, school database organizes the data about stud teachers, and admin staff ete. which helps in efficient retrieval, inse and deletion of data from it. Basic building block of Database is Data. The input to dalabe known as data and output as information. Data and Info: ion Most people believe that the terms “data” and “informal interchangeable and mean the same thing. However, there difference between the two words (148) [Compute weve pe utilize reed and aT for processiN information is data formatted in 4 by human beings in some may I data is called information. When raw facts and a anged in some proper order then they become fon, Information has proper meanings. Information is useful in cme making. Actually We process data to convert i into information, ror Example: 1 X¥Z,15 etc Ord, number, Data something”, Data in ,e Processed to make it be inthe above example the three data items have no meaning. But if ganize these items in following way. then they collectively represent -ningful information. Roll_No Name Age 1 XYZ 15 Information is created from data~~ “The Figure 1 depicts the data processing cycle, where days, rocessed into required output fg processor for it to be process ra PUL tn the "li information. ‘ora x0 | NAME | ADDRESS | RAM DELHI mom [ace | 1 xooccaG | 18 2 RAMESH ‘SHIMLA suytt | DHARAMSHA pooKG0K | 1g 3 XX 20 IRESH DELHI 30000 f _|sui Lt 7 a pata is stored in records. A record aiains all the data about one particular DIKW (Data Information Knowledge Wisdom) DIKW is the model used for discussion of data, Knowledge. wisdom and their interrelationships. It represen or functional relationships between data, information, knowlege wisdom. 4 composed of fields and Person, company, or item in a guabase. Records appear as rows in the database table. A record far o.NO 3 is highlighted in table Student. ez Student ~| "ott_No | NAME ADDRESS PHONE, AGE 100 miles 1 RAM DELHI XXXXXXXXXK 19 | | if 2 |RAMESH_ SHIMLA | i eeeitadie L —fL XXXXOOOKK 8 ieee cigs 3 SUJIT DHARAMSHALA | XXXXXXXXXX_ 20 “tomiesisaumeeferdsance a _|sures DELAT XXOOCOORK | 19 © Fields Afield is part of a record and contains a single piece of data for the ‘bet of the record. In the database table illustrated in . each record ‘niains five fields: ROLL_NO,NAME.ADDRESS, PHONE,AGE.Fields ‘Paras columns in a database table. Data from the NAME field for four “ts is highlighted in the table Student below : ‘Student ADDRESS FROME * this very difficult to walk 100 miles by any person, but vehicle transpot isokay a DATABASE ELEMENTS © Tables wo A database table 1s composed of records and fields that hold a XXXXKXKAK Tables are also called datasheets, Each table in a database aa DELHI . about a diferent, but related, subject. Below is example of datab™ SHIMLA | XXXXXXXXXX a Student = (SHALA | XXXXXXXXXK DHARAM: ol won | 19 | DELHI | XXXxMoe’ | je (150)fon of Information stored in di ‘c is called instance of jatabase 1 instance of tim 5 The overall design of the d DBMS (DATABAE MANAGEMENT SYSTEM) “The anain purpose of database management system is {0p the dam, Coneider a sehool that Keeps the data of Students, teacher, courses, books etc. To manage js data we need to store this tay courses, Pon ge can add new data, delete wimused data, opi aoa data, retrieve data, to perform these operations on data We ne oad management system that allows us to store the data in such, aaa (all these operations can be performed on the data efficeng, DBMS stands for Database Management System. A Data Bay eomnt system is a system software for easy. efficent and rela Gata processing and management. It can be used for: 5 Creation of a database. Retrieval of information from the database. Updating the database. Managing a database. \We can break it like this DBMS = Database + Management System Database is a collection of data and Management System is a set o programs to store and retrieve those data. Based on this we can define DBMSlike this : DBMS is a collection of inter-related data and set of programs to store & access those data in an easy and effective manner. For Example, MySQL, Oracle ete. are popular commercial DBMS fferent applications. led database seh Mana llows users the following tasks 4 Data Definition: It helps in creation, modifics of definitions that define the organization of data 2 Data Updation: It helps in insertion, modification and de! the actual data in the database. «1s2) saws AP retrieval : It helps in retriey fi val of data fro me Reco fe al of da bm the database wl ninistration : It helps in registe . init : in registering and monitoring user cing data security, monitoring performance taining data integrity, dealing with co Fe ng information corru ae with concurrency control and recov" ted by unexpecte ure. plications “sppttcations where we use Database Management Systems are: Tretecom : There is a database to keeps track ofthe ino regarding calls made, network usage, customer detai (without the database systems it is hard to maintain that Jmount of data that Keeps updating every millisecond, industry | Where it is a manufacturing unit, warchouse or distribution centre, each one needs a database to keep the fecords of ins and outs. For example distribution centre sho keep a track of the product units that supplied into the centre as well as the products that got delivered out from the distribution centre on each day; this is where DBMS comes into pi Banking System : For storing customer info, tracking da credit and debit transactions. generating bank statements et this work has been done with the help of Database systems. Sales : To store customer information, prodi and invoice details. Airlines : To travel though this reservation in database. es, we make early reservat Education sector : Database systems are schools and colleges to store and retrieve student details, staff details. course detal —ee 7 ‘Amazon, Flipkart etc. These site, Sst websites such as ur addresses and prefereng product information, y details and provide you the x your query. All this involves a Database management fins) tem, relevant list of prod COMPONENTS OF DATABASE SYSTEM ‘A database system is composed of following component, , Data : Hardware Software Database Access Language Users which coordinate with each other to form an et = fective database Software Language DATA - Data is that resou pata Ce, fOr Which : ich DBI motive beh ind the creation of DBMS ig MS is designed, The data. 12 a typical Database, the user saved b, store and utilize eta data is stored ata 18 present and Metadata is data about the data, This is ino the DBMS to better understand the data st ae noe stored in i. ror example : When you store yourNai une in a database, DBMS will store when the name was stored in a ae peat is the size of the name, is it stored as related data to some Hardware- When we say Hardware, we mean computer, hard gisks, 1/O channels for data, and any other physical component involved before any data is successfully stored into the memory.When we run MySQL on our personal computer, then our computer's Hard Disk, our Keyboard using which we type in fall the commands, our computer's RAM, ROM all become a part of the DBMS hardware. Software- This is the main component, as this is the program which controls everything. The DBMS software ts more like a wrapper around the physical database, which provides us with dan easy-to-use interface to store, access and update data, The DBMS software is capable of understanding the Database Access Language and interpret It into actual database commands ‘0 execute them on the database. Data base access language- simple language designed to write comm: update and delete data stored in any database: decks me Cha atsioce taco Ieee ara DEMS for execution, which is then translated ond e504 by the DBMS. User can create new databases tables, insert on Des ciseeita opeicow a ne ae using access language. ) icon Database Access Language is @ wands to access. insert, ‘Auser can write oxibasis of the job provided access to ‘The various types of users which can access the database 4, © Application Programmers RDBMS (Relational Database Management System) RDBMS RDBMS stands for “Relational Database Management System, An RDBMS is a DBMS designed specifically for relational databases Relational Database Management System (RDBMS) is an advarced version of a DBMS system. It came into existence during 1970's. RDBMS system also allows the organization to access data more DBMS, iently then Relational Database - A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it casy to locate and access specific values within the database. is “relational” because the values within each table are related to each ther tables. The relational structure makes it possible to run queries across multiple tables at once. A relational database management system (RDBMS) is Program that allows you to create, update, and administer relational database. Mi I database management systems use labase. An RDBMS Is a type of DEMS is and th Kind of system, data is agen EBON aS tuples and S te vant S¥8tem and is widely 'S MYSQL. Oracle, SQL Server, to . eet and store RDBMS is a powerful data ma “4s the world. Example of RDB eastional File Systero ira aaitional File Processing Systems ; where all theinformation is stored in different cnn, Jes. s2etaditonal Ales system stores data in a manera 159 ments ofa OFgAnALOn have ther own sto set ee, seoeaundancy. is totally computer based ut this system is good only for small organizations having small umber of items. In this traditional fle system, each file is independent of wiper file and data in the different file can be integrated only by writing an iividual program for each application. A file system is a method for sorting and organizing computer fies and the data they contain to make it easy to find and access them. To illustrate Traditional File Processing Systems definition, lets us take an example of school where student record for examination is stored in other file and his library record is stored in different file that creates many duplicate values like roll Number, Name and Father Name Characteristics OF Traditional File Processing System © Itstores data of an organization in group of files. © Files carryingdata are independent on each other. ew : used to design th © COBOL, C, C++ programming languages were used ign files.© Itis very diffi in file processing sy: © Any change in programmer. in Traditional File Processing Systems are called ‘Traditional File Processing Systems n computer based sy nor Traditional File Processing Systems i 3th Traditional File System Problems in Traditional File oriented approach © Data redundancy : Data redundancy refers to the duplication of data, lets say we are managing the data of a school where a student is enrolled for two courses, the same student details in such case will be stored twice, which will take more storage than needed. Dat redundancy often leads to higher storage costs and poor access time. Data inconsistency : Data redundancy leads to data inconsisten" let's take the same example that we have taken above, a stud (158) requests t Adress. stoy eat © change his addrese gre ee and noo all hes ars m ren this can seolation : Because data are scattere be in different formats, writing n “tin various fies, and es ™2Y ew ay fp rewseve the appropriate data is difficutt. pendency on application programs Ch Prange in application programs, janging files would lead pplication programs one the ame. aciD a )transaetion properuies. Atomicity of a transaction refers to “Al or nothing which means a i the operations in a transaction executes of pone. For example: Let's say A transfers 100 rupeesto Bs account ‘This transaction consists multiple operations such as debit 100 rupees from A's account, credit 100 rupees to B's account. Like any other device, a computer system can fail let's say it fails ater first gperation then in that case A's account would have been debited by 100 rupees but the amount was not credited to B's account, in such case the rollback of operation should occur to maintain the atomicity of transaction. It is difficult to achieve atomicity in file processing systems. ¢ Difficult Data Access : A user should know the exact location of fle to access data, so the process is very cumbersome and tedious IT user wants to search student hostel allotment number of student from 10000 unsorted students’ records. how difficult it can be. © Unauthorized Access : File System may lead (0 unauthorized fs file having his marks, he access to data. If a student gets access (© M can change it in unauthorized way: No Co: . ‘of same data by multiple users meurrent Access : The access tatty does nt all at same time is known as concurrency: Fe ‘ime. ca by only one user al currency as data can be accessed by ony ae—— ‘© No Backup and Recovery : File syst backup and recovery of data if a file is I © Data Security : Data for example a student in a payroll details of the teachers, difficult to apply in file process © Limited Data Sharing : There with the traditional file system. Each applic files and users have little choice to share the data out applications. Complex programs required to be writ data from several incompatible files. Redundancy : Unlike tragit; yout! in DBMS is very less of gar e*¥stem storage De: Pi ndaney & Pot present. Data rede edn hen the same data are storeq uunnecessacte ancy out? © ua redundancy ts reduced or eliminated n ave ese 'n DBMS because ation rather than being each application. No data access time Maia are stored at @ centralized ol ted by individual users and for ascatlo” saves storage and improves dip inconsistency can be avoided ; date the changes in other application, given both have the sam Mr of details. While this 1s not the case with DEMS systems ns there is a single repository of data that is defined once and is seccssed by many users, and data are consistent. » Searching capability : Searching and retrieving of data is very easy in DBMS systems. The need to write separate programs for each of the search is eliminated as in the case with a traditional file-based approach. In DBMS, we can write small queries to search for multiple information at a time from the data from database servers. » Data Sharing : Data Sharing is the primary advantage of Database management systems. DBMS system allows users and applications toshare Data with multiple applications and users. Data are stored in one or more servers in the network and that there is some software locking mechanism that prevents the same set of data from being changed by two people at the same time. While the file system doesn’t have this capability. * Data Security : DBMS systems provide a strong Cee Protect data privacy and security. DBMS ensures that oF 7 authorized users have access to data and there js a mechanism ‘efine access privileges in DBMS. 7 Flexibility : Database systems are more flexible than fle P ‘ystems, ‘ In traditional file system. Difference between DBMS and Traditional File System ‘© DBMS is very expensive but. the traditional file system is cheap, ¢ DBMS is good for the large system but, the traditional file system g good for a small system having a small number of items. © DBMS required lots of effort for designing but, the traditional fy system is very low design efforts. © DBMS is highly secured but, the traditional file system is secure. ¢ DBMS is data sharable but, the traditional file system is isola data sharable. ¢ DBMS is flexible but. the traditional file system has a lack « flexibility and has many limitations. ¢ DBMS has no integrity issue but, the traditional file system has © integrity problem, *¢ DBMS has a complex backup system but, the traditional file syste has a simple backup system, Advantages and Disadvantages of DBMS ADVANTAGES : . ‘There are several advantages of Relational Datab ‘management system. Few of them are as follows (160) ‘Omputer Science-XI)ae Data Integration : Data integration is a proce, ;chitecture of DBMS o SS of e1 ON collection of inter- relateq data BMS Mita. Database systems are my 2 Breer t plex 5 tte main purpose of DBMS jg tg Provide users a se of the data .To ease the user interaction with database, e act ge internal irrelevant details from user This . 7 Pt? ant details from user is called data abst int eve levels of data abstraction ; = and set ade-up much feasibility. © Easy access to data: A database system manages dal, way so that the data is easily accessible with fa, 3 th St response © Concurrent access to data : Data can be acces, Sed cong, different users at same time in DBMS, ren, © Data Backup and Recovery : This is another Advantage g as it provides a strong framework for Data backup, users required to back up their data periodically ang mansy automatically taken care by DBMS. Moreover, in case of 4 crash, DBMS restores the Database to its previous conan, © Improved decision making : Better-managed data ang in data access make it possible to generate better-quality Informatig on which better decisions are based. ternal oF Physical level : Actual physical storage structure Lan ‘gecess paths. S ceptual or Logical Level : Structure and constraints for the a B oir database External or View level : Describes various user views 8 External level DISADVANTAGES OF DBMS : © DBMS requires high initial investment for hard Iware, software ani trained staff. in order to convert our data into a Database Management we need to spend a lot which adds on to the cost of the Databae Management System. 'n order to work with a Database Management System a team d ‘echnical staff is required who understand DBMS. A DBMS requires disk storage for the data and sometimes you ‘0 purchase extra space to store your data, Database systems are complex to understand DBMS does not give a Backup and recovery increased complexity Several users conc S00d performance as its speed is ve! d is are more difficult. This is becat! cs and because databases are often process rently. (162)Internal Level It is physical representation of the database on the compu, | level. This level describes hoy "4, level is also known as physical the actually stored in the storage devices. mn Physical level is also responsible for allocating space ;, This is the lowest level in the three level architecture. The phyg also discusses compression and encryption techniques, Conceptual Level It also called logleal level, The whole design of the database as relationship among data, schema of data ete. are described fy level. Database constraints and security are also implem ented level of architecture, This level is maintained by DBA { at ‘ministration. The conceptual eve doesnot care for how the dat wat database is actually stored. External Level internally fetched from database with the help of conceptual and inter level mapping. The user doesn't need to know the database schema details sucha data structure, table definition etc. user is only concerned about dala which is what returned back to the view level after it has been fetch from database (present at the internal level). External level is the “top tevel’ of the three level DBMS architecture. This is the highest level inte three level architecture and closest to the user. ‘The external level only shows the relevant database content to be users in the form of views and hides the rest of the data. So ifferet users can see the database as a di requirements. An external sche ifferent view as per their incividud ema describes the part of the which specific user is interested in. It hides the unrelated details of tt database from the user. There may be “n" number of external views ff each database. este At able. on view Hs Just the content ofthe daag nal ticular user. For example Se 88 itis seen by © user ce only sales related data et from the sales will 5 1 Let's say we are storing stud ie: oo cal level these records cay cs, gigabytes. terabytes ete) in memory. There details ar the programmers. from no eal level these records can be described as feds and jong with their data {ypes, ther relationship aneng nt jogically implemented. The programmers ge ve nerally work at. se they are aware of such things about database ssteme nt information in a be described as blocks becaus evel, user just interact with system with the help of GUL ateie’ ae ee data is stored; such details are hidden from then - DBA (DATABASE ADMINISTRATOR) ol cally there are three types of users for a DBMS. They are : details at the screen, they are not aware of how the data is Application Programmer : These users write application an to interact with the database. Application programs en be written, in some programming language such a COBOL, PL/I, C++, JAVA or some higher level fourth generation ng. Such programs access the database by issuing the appropriate request, typically a SQL statement to: DBMS. The End User : End users are the users, who use the applications developed. End users need not bd es ie working, database design the access mechanism te They iS use the system to get their task done. End users are o a) Direct users _b) Indirect users Soo ®) Direct users : Direct users are the users wht te computer, database system directly. by ees wing the Provided in the user interface. They ime he application programs already en Sanne desired result. E.g. People at railway T Who directly interact with database (eae Tense]by Indirect users : Indirect users are those benefit from the work of DBMS indirect outputs generated by the programs, for dec, any other purpose. They are just concerned with and are not bothered about the progra system Analyst : System Analyst deter: end users, especially naive and parametric eng develops specifications for transactions that me." requirements. System Analyst plays a major rote in 4 design, its properties; the structure prepares the requirement statement, which involves the feasibiity economic aspect, technical aspect etc. of the system, a Sieg pe ‘The Database Administrator (DBA) Database Administrator (DBA) is the person which makes i strategic and policy decisions regarding the data of the enterprise, who provide the necessary technical support for implementing thee decisions. Therefore, DBA is responsible for overall control of the ata technical level. In database environment, the primary resource isi. database itself and the secondary resource is the DBMS and software administering these resources is the responsibility of Database Administrator (DBA). DBA responsibilities Ps include designing, implementing, = = ining the database system; establishing policies and proceduss ‘taining to the management, security, maintenance, and use ol database management 1t_system; databas management and use. and training employees in dal ‘he role of the DBA the following fe ve ed by & functions : ry important and is defin 1. Schema Definition : © The bs ae define the logical Schema of the dal pee te 19 the overall logteal structure of the A i rent this schema, database will be developed red data for an organization. oe age structtre and ACCESS Method Def vane DBA decides how the data is to be ition : # sored database. epresented in the posistin€ Application Programmers ; ides assistance ‘The DBA prow! to application * Gevelop application programs programmers to poysical Organization Modification; rhe DBA modifies the physical organization of the database to reflect the changing needs of the organization or to improve performance. approving Data Access : ‘the DBA determines which user needs access to which part of the database. «According to this, various types of authorizations are granted to different users. Liaising with Users : The DBA needs to interact continuously with the users to understand the data in the system and its use. The DBA figures out which client needs access to which part of the database Monitoring Performance : The DBA monitors performance of the system. The DBA ensures that better performance is maintained by making changes in physical oF logical schema fi required. not be lost oF jdically backing up the servers. In case of failure vred from this backup. Database should Backup and Recovery : damaged. The DBA ensures this Peri database on magnetic tapes or remote Such as virus attack database is recover MA DictIONARYall up-to-date information about the objects like constraints, functions ete. 7 i is very important as : The data dictionary is very E such as what is in the database, who 1s allowed t r a ee database physically stored etc. The users of the databa. interact with the data dietionary. 118 only handed by qr administrators. following * Names ofall the database tables and their schemas, ‘The data dictionary in general contains information stout 7 the * Details about all the tables in the database, such owners, their security constraints, when they were erexy ‘* Physical information about the tables such stored and how. © — Table constraints such as prim: information ete. 8 where they ap ary Key attributes, foreign ixy Information about the views of the database. ¥t also contains the physical information of the table like about their storage, about their alteration, etc. Why do we need all these information ? I makes us easily identify access and understand the facos about the object. One can imagine data dictionary as storing information about house like house name, address, how many live in the house, wh? nouns iest/youngest person, responsibiies of each member i Ot ause le-The data dictionary contains the bookkeeping information about the database so that it can manage the data. It does not contal® i physical information like where isthe data stored fr te on tural information like its attribute + Sis. constraints and indexes, pe table below is an example of a typical data ek = Tomo he errs ames and its data ce ‘There are two types of data dictionary - Active and Passive. dive Data Dictionary ‘Any changes to the database object structure via DDLs will have to Yemilected in the data dictionary. But updating the data detenary tiles for the changes are responsibility of database tm wich : ‘tionary exists. If the data dictionary is created in = a tim the DBMS software will automatically agreperel — ah lince there will not be any mismatch between os seen po fe data dictionary details. Such data dictionary “onary Naive Data Dictionary ely from. format is created separat i: ‘he information of the actual date of the database, Without the prese™ ty 2 80me of the databases, data ape to store oy a of a data dictionary, a databa manage system cannot access Surrent database as entirely new = smi, excels oF data from the database eaéement svete al Database administrators handle t dicti ‘tionary, and users don't interact with it, (168) onary ored data Information. Sometimes #16 tr Tred to ker dae Stee an effort data dict format. In such case. iad “nary in syne with the database objects:rraints : Domain int q const! ‘egrity means the ak sive data dictionary. In this case, there ig a ch oss of values for an attribute. It ensi ee a an, set of vall ensures that only av. This ing f value is allowed to be stored in a column led pas erat ‘with the database objects and the data dictionary, ¢ handled with utmost care, range o ‘pomain constraints can be defined as the definition of a valid Integrity Constraints * get of values for an attribute, Database integrity refers to the validity and consisten me data type of domain includes string, character, integer, data. Integrity is usually expressed in terms of constraint * time, date, currency, ete. The value of the attribute must be Data Dictionary has to b Y Of sto re S. Which ay consisteney rules that the database is not permitted 1g a available in the corresponding domain. Constraints may apply to each attribute or they may 7 a bles. relationships between tables. xan] Tr Integrity constraints are used to ensure accuracy and consistengy 2 data in a relational database. Data integrity is handled in a relate, LL_NO NAME MARKS aes database through the concept of referential integrity. =i B . © Integrity constraints are a set of rules. It is used to maintain the 1 RAM 90 quality of information. RAMESH, 50 18 2 © Integrity constraints ensure that the data insertion, updating, and ther processes have to be performed in such a way that data 3 Sus 2 * Integrity is not affected, © Thus, integrity constraint is used to guard against accidental damage to the database. 4 SURESH 95 18 5 HARSH 85 = © For example, A blood group must be ‘A’ or ‘B' or ‘AB’ or ‘0’ only (can not any other values else), te, because it is an integer Here A is not allowed in AGE attribut Types of Integrity Constraint tise 2 Entity integrity constraints © The entity integrity constraint s sant be null used to identify © This is because the primary key ee ear individual rows in relation and if the Lad value, then we can't identify those ee Pe = © A table can contain a null value other eae field, tates that primary key valueHere ROLL_NO attribute is not allowed as Prim, primary key can't contain NULL value. 3. Referential Integrity Constraints © A referential integrity constraint is specified between tn tables. ALY ey because © In the Referential integrity constraints, if a foreign key in Table I refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be available in Table 2 Example : Foreign Key of table student SUBJECTD is not allowed as Foreign Key of table Student Le UBJECTID $003 is not defined as Primary See ca sae 4, Key constraints ¢ Keys are the entity set that is used to identify an entity within its entity set uniquely. e An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table. Example : E Student NAME ace | SUBJECT.D 1 RAM 18 S001 2 | Ramesi 18 so 3 SUJIT 20 Soe 2 SURESH 18 Key because all Here attribute ROLL_NO is not allowed as Primary ‘"®S must be unique. SL Datat: mo Dusigpes are roles eat define what at 2 MOT, tt and how that data is actually stored ‘Ul reasons : that can be of data ype umn © bata types enables us to restrict te merc data PE Ct Stored in a column. For example, ® ric values.Wt specifies @ character string now you are only characters wide, if you os ie jum size a value in this cotumn| can have is n bytes, Each value characters. 4. Data type allow for alternate sorting orders. tn SQL there are three main data types : aS you specify 2a short blanks are added if the length is 2 Date and time then blanks shorter than maximum length n, 2. String type put the size of value | 1, Numeric Data Type : Numeric data types are normally used to store numeric datz like age, marks, salary, price ete. Numeric datatypes are following |. int - used for number without decimal. Il Decimal{m.d) - used for floating/real numbers, Here m denotes the total length of number and d is number decimal digits, 2. Date and Time Data Type : 1. date used to store date in YYYY-MM-DD format remains 1 bytes troduction to sgL gol stands for Structured Query Language, which isa andardied Ianguage for interacting with RDBMS (Relational Database agement System)-Structure Query LanguageSOl) is & database nay language used for storing and managing data ip Relational DBMS. $1 vas the first commercial language introduced for EF Codd’s Tuutional model of database. Today almost all RDBMS (MySql. Oracle, hiomis, Sybase, MS Access) use SQL as the standard database Ny linguage. SQL is used to perform alll types of data operations in RDBMS. I time -used to store time in HH:MM:SS format. 3. String Data type : String data types are normally used to store names, address deserpuons or any value that contains letters and number inary data, like image or audio files. String datatypes are following 1. char (n) ~ used to store a fixed length string. Here, n denotes maximum number of characters. varchartn) Characteristics of SQL ring : © SQL stands for Structured Query Language. It's a Sued and managing data in relational database manager (RDBMS). . and SQL © Al the RDBMS tke MySQL: fnformss. Oracle, MS ASS Server use SQL ax their starciard dsalaot eA 2 ~used to store a variable length string. lere, n denotes maximum number of characters.oo, © sQLallows users to query the database in a number English-like statements. Ways % «SQL is used to perform C.RU.D (Create, Retrieve, Upda, . Updat ; operations on relational databases. ty © SQL can also perform administrative tasks on database database security, backup, user management etc. SUCh ay We can create databases and tables inside database using gy © Structure query language is not case sensitive. Generally, e of SQL are written in uppercase. * keywor ‘© Statements of SQL are dependent on text lines. We can u; ‘SQL statement on one or multiple text line. aon: © Using the SQL statements, you can perform most : }ost of the actions ing © SQL depends on tuple relational calculus and relational algebra, SQL Commands : SQL commands are instructions, coded into SQL statemeris, Which are used to communicate with the database to perform spectic tasks, work, functions and queries with data. SQL commands can be used not only fc for searching the database but also to perform various other functions like, for example, you a oe aio data to tables. or modify data, drop the table, st Permissions for users. SQL commands are grouped into four maor categories depending on their functionality: ee © Data Definition Language (DDL). DDL or Data Definite to aaa actually consists ofthe SQL commands that can be ust! define the database schema .These SQL commands are used ting, modifying, and dropping the structure of database ob/e** ‘Examples of DDL commands : 4 : CREATE ~ 15 used index, to create the database or its objects (uke function, viev 4 DROP - is . Store procedure and triggers). ‘sed to delete objects from the database. 176) [Com se is used to remove all records from an 5 ed for the records are lata gor oi used to add comme Pe rf se 2 gonipulation Language (DML) - The SQL commands Be Mn the manipulation of data present in database behets pu oF Data Manipulation Language These SQL commen”, a feed fr storing retrieving, modifying, and deleting data. used (0 alter the Structure ofthe dy ase saspies of DML | mepgcr is used to retrieve data fom the database | gseRT _ is used to insert data into a table. 2 BATE - is used (o update existing data within a tale » DELETE - is used to delete records from a database table. stnsection Control Language (TCL) - These SQL commands are ised for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT. TCL commands deals with the transaction within the database. Eumples of TCL commands : 2 COMMIT- commits a transaction. 2 ROLLBACK- rollbacks a transaction in case of any €ror ou + SAVEPOINT-sets a savepoint within a transa 2 8T TRANSACTION-specify characteristics for the transaction ‘Dela Control Language (DCL) - These SOL coma Se Moviding security to database objects. These comm or and Oy EVOKE which mainly deals with the ris: Pe controls of the database system. s q.tmples of DCL commands : BART gives user's access privileges (0 a ay wae CORE withdraw user's access Pres DG commana. \Gonputer Science-xin]Ww names of the columns, ty we Pe of _ = pont Pine data etc. Let Us now dive ings ata to be stored in ae ALTERDROP.RENAME, TRUNCATE 3,. a iE statement to create tables in sqy | etals on how to se = wich felps in defining objects. gq) ng, Mr me rabies -The actual data is stored in tables ang egy ts fF gf apie table_name | oor DDL coneainer of Sian s sored in 2 database Sch taty « CREATE ( ‘There are two CREATE statements available in sqy , nnd. date 9 = CREATE DATABASE 3 data_type(size) 2 CREATE TABLE CREATE DATABASE A Database is defined as a structured set of data. So. i so: bles "name of the Arst coh. very fist step to store the data in a well structured manner isto cre = of data we want to store in the coun Gausbese. The CREATE DATABASE statement is used to create 2 --| ia se: TP Pe a particular database in SQL example. teger data. Syntax “Wyze: Size of the data we can store in a particular column. For CREATE DATABASE database_name: caple if fora column we specify the datatype as int and size as 10 jeathis column can store an integer number of maximum 10 digits. Example : This query will create a table named Students : CREATE TABLE Student ( pame: name of the table. database_name: name of the database. Example: ‘This query will create a new database in SQL and name ‘| database as my_database. __ CREATE DATABASE my_database: (© You can USE statement to use the database and make t = ROLL_NO int(3), for camp: NAME varchar(30), mae ee ADDRESS varchar(30), = Saeiinaaemmemiseesseeeeal PHONE_Noint(12), a AGE int(a), data we need cre tbe about creating databases. Now create a table in Ste The CREATE TABLE statement ® °° Student. The eae We know that a table comprises of ™* ion fi Query will create a table g aype el ae of So whit Creating tables we have to provide all the (178)‘ADDRESS are of type varchar and can 9 ie is created most natural specifies that these fields can hold maximum of 30 characters» Wa table 15 hi ing to d nipulated later. This data can bo eat tis table can be inserted by using yen i SER INTO statement of SQL is used to inse eee ‘are two ways of using INSERT INTO statem ble aty valves ¢ First method is to specify only the value of data to be s.osl7 Mj without the column names. nse © DROP DROP is used to delete a whole database or just a table DROP command is used 0 remove an object from the database drop a table, all the rows in the table is deleted and the table str, removed from the database. Once a table is dropped we cannoy .°* back, so be careful while using DROP command. When a table is qq all the references to the table will not be valid. OPP Syntax : DROP TABLE table_name; table_name : Name of table to be deleted. DROP DATABASE database_name; database_name: Name of the database to be deleted, Example : To drop the table Student table, the query would be like DROP TABLE Student ; © TRUNCATE The gy TL a new row in lent for inserting syste * (gERT INTO table_name YAWUES (valuel, value2, value3...valueN}; able name: name of the table. aluel, value2,..: value of first column, second column... for the sea record ‘While inserting a row, if you are adding value for all the columns of se table you need not specify the column(s) name in the sql query. But need to make sure the order of the values is in the same order as the \lumns in the table. When adding a new row, you should ensure the TRUNCATE statement Is a Data Definition Language (ppt) |{iiype of the value and the column matches. operation that is used to mark the extents of a table for deallocation 2.Column names and values both : In the second method we will an for reuse). The SQL TRUNCATE command is used to delete all te | specify both the columns which we want to fill and their rows from the table and free the space containing the table. corresponding values as shown below: Sy z eaat INSERT INTO table_name 7 .TE TABLE table name; Ucolt, cot2, col3,...colN)] ame Name of the table to be truncated. VALUES (valuel, value2, valued,...valueN}: ‘able name: name of the table. To delete: all the . Tows from Student “lL: name of first column, second column t table, the query would be lit TRUNCATE TABLE Student; Value! umn... the pent? Value2, values : value of first column. second colu Rew record, (180)NOTE : When adding a row, only the characters 9, should be enclosed with single quotes. New row can be inse, INSERT command only one row is inserted at a time, Example : INSERT INTO Student VALUES (1. ‘“RAM’, ‘DHARAMSHALA’,XXXXXXXXXX, 18); If you want to insert only roll number name and age 5 student table, the query would be like, INSERT INTO Student (ROLL_NO, NAME, AGEVALUES (1, Paw, 18): © SELECT Select is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetch data from a database, We can fetch either the entire table or according to some specified rules. ‘The data returned is stored in a result table. This result table is also, called result-set. With the SELECT clause of a SELECT command statement, specify the columns that we want to be displayed in the query rest optionally. which column headings we prefer to see above the resi ‘Syntax : ‘SELECT column] ,column2 FROM table_name; column, column2: names of the fields of the table table_name: from where we want to fetch ‘This query will return all the rows in the table with fields colum=!: column 2, To fetch the entire table or all the fields in the table: SELECT * FROM table_name: [NAME | [RAM | RAMESH SUJIT a 3 Ee SURESH | 5 HARSH if PRATIK ample Queries quay to fetch the fields ROLL_NO, NAMB, AGE from the table ' student: SLECT ROLL_NO, NAME, AGE FROM Student ; Output + [Rout_No [NAME AGE 1 RAM 18 2 RAMESH 18 3 SUJIT. 20 4 ‘SURESH 18 5 HARSH 19 6 PRATIK 20 ‘Tfetch all the fields from the table Student: SLECT * FROM Student; Output : = NAME RAM RAMESH SUJIT SURESH HARSH PRATIKWHERE Clause WHERE clause 18 used with SELECT condition based 07 which rows will be extracte keyword is used for fetching filtered data in a re: vinis used to fetch data according t0 a particular cry ¢ WHERE keyword can als is patterns. syntax: SELECT column1,column2 Iso be used to filter data y Y May FROM table_name WHERE column_name operator value; columnl , column2: fields in the table table name: name of table column_name: name of field used for filtering the data operator: operation to be considered for filtering value: exact value or pattern to get related data in result List of operators that can be used with where clause : Description Greater Than > Greater than or Equal to < Less Than <= Less than or Equal to = Equal to 2 Not Equal to i eS Search for a pattern To specify multiple possible values for @ CONS gxample Queries 4 To fetch record of students from table Student with age equal to 20 ELECT * FROM Student WHERE AGE=20; output + ROLL_NO NAME ADDRESS | PHONE_NO | AGE 3 ‘SUuIT | DHARAMSHALA | XXXXXXXXXX | 20 6 PRATIK MANDI XXXKXXXK | 20 ¢ To fetch Name and Address of students from table Student with ROLL_NO greater than 3 SELECT ROLL_NO,NAME.ADDRESS FROM Student WHERE ROLL_NO > 3; Output : ROLL.NO | NAME | ADDRESS 4 ‘SURESH DELHI 5 HARSH ‘SOLAN 6 PRATIK MANDI | BETWEEN operator ‘The BETWEEN operator is used to specify a range of values. The range you specify contains a lower and upper range Its used to fetch filtered data in a given range inclusive of two values. Syntax : SELECT columni,column2_— FROM column_name BETWEEN valuel AND value2: BETWEEN : operator name valuel AND value? : exact value from valuel to value2 to get table name WHERE elated data in result set. Example Queries © To fetch records of students from Student table where ROLL_NO is between 1 and 3 (SELECT * FROM Student WHERE ROLL_NO BETWEEN vy EN 1 AND 3 Output : ROLLNO | NAME | ADDRESS | PHONE No 1 RAM DELHI XXXXX 2 RAMESH, SHIMLA _ | XXXXXxx u 3 ‘SUJIT _| DHARAMSHALA | XOXXXKXXX ; 20 ¢ To fetch NAME,ADDRESS of students where Age is betwe 30 (inclusive) ‘sen 20 ang SELECT NAME.ADDRESS FROM Student WHERE 20 AND 30; ‘Ade BETWEey Output : (NAME ADDRESS [sustr DHARAMSHALA PRATIK | MANDI LIKE operator aoe not always know the exact value to search for. You can select rows that match a character pattern by using LIKE operator which is used to fetch filtered data by searching for a particular pattern in WHERE clause. LIKE operator is used with CHAR datatype Syntax : SELECT column, column_name LIKE pattern; column2. FROM table_name WHERE LIKE : operator name Pattern : n inemitec exact value extracted from the pattern to get related 4a! Note : The ch: aracter(s) in pattern Example Queries pattern are case sensitive. starts © To fetch records Sica se of students from Student table where NAME spec * FROM Student WHERE NAME LIKE ‘S¥: ‘ene (wildcard) signifies the later characters here which can be of th and value. aay tenet output: ROLL_NO NAME ADDRESS PHONE_NO | AGE 3 ‘suJIT_| DHARAMSHALA XXXXXXXXXX | 20 4 SURESH DELHI XXXXXXXXXX | 18 +s To fetch records of students from Student table where NAME contains the patter ‘AM’. SELECT * FROM Student WHERE NAME LIKE %AM% Output : ROLL.NO | NAME 1 RAM 2 RAMESH PHONE NO | AGE XXXXXKAAXX | 18 XXXKKXXXXX| 18} ‘ADDRESS: DELHI ‘SHIMLA, IN operator it is used to fetch filtered data same as fetched by ‘= operator Just the difference is that here you can specify multiple values for which we can get the result set Syntax : SELECT _columnl,column2FROM column_namelN (valuel.value2,..): IN : operator name valuel,value2,..: exact value matching the values Even and get related data in res Example Queries © To fetch NAME and ADDRESS of students from table Age is 18 or 20. SELECT NAME, ADDRESS FROM Student WHERE Age IN (i! tablename WHERE 1e Student where 8,20); (les, —_ttt sample Queries : Pant DELETE » To fetch all the records from Student table where Age is 18 and address is DELHI. RAMESH sus SELECT * FROM Student WHERE AGE = 18 AND ADDRESS = SuuIT DHARAMSHALA peut: SURESH DELHI ‘Output : PRATIK MANDI [Rou.no | NAME [| ADDRESS | PHONENO [AGE © T fetch records of students from table Stadent_ where ROLL No, : a DELHI | 2000000008 {18 las : 4 SURESH DELHI XXXXXXXXXX_| 18 SELECT * FROM Student WHERE ROLL_NO IN (1,4); To fetch all the records from Student table where NAME is Ram Output : and Age is 18. ROLLNO | NAME ADDRESS | PHUNE NO asi] SELECT * FROM Student WHERE AGE = 18 AND NAME = ‘RAM: 1 Raesi | DELHI [30000000 [18 Output : 4 suresh | Deuat | e000ce00K [16] 0 AND .OR and NOT operators In SQL. the AND & OR operators are used for filtering the data and setting precise result based on conditions. ® The AND and OR operators are used with the WHERE clause. . These two operators are called conjunctive operators, i oe This operators displays only those records where OR Open omtion! and condition2 evaluates to True. of the conmertet : Tis operators displays the records where either one Isether condition and condition? evaluates to True. Tht ss ‘on! is True or condition? is True. AND Operator Syntax; SELECT * FRO; - ond and ...conditiony ; t @>I€_name WHERE condition! AND conditi table name. “name: name of the table SELECT * FROM table_name WHERE condition! OR condition2 OR... condition ; table_name : name of the table condition1,2,..N : first condition, second condition and so on Sample Queries : To fetch all the records from Student table where name is RAM or ame is SUJIT. suypSELECT * FROM Student WHERE NAME = "RAM OR NAME = Mr; Output : = Name | appress | PHONE No [AGE 1 RAM DELHI [xGCOOAKKXX | 18 3 SUJIT__| DHARAMSHALA | XXXXXXXXXX_|20> se © To fetch all the records from Student table where name , s age is 20. RAM » By default ORDER BY sorts the data in ascending order. SELECT * FROM Student WHERE NAME = ‘RAM’ OR agp _ ¢ We can use the keyword DESC to sort the data in descending order aa = 20, and the keyword ASC to sort in ascending order. ROLL.NO | NAME ‘ADDRESS » Sort according to one column: To sort in ascending or descending : am Derr order we can use the keywords ASC or DESC respectively. 3 susiT_| DHARAMSHALA aoe SELECT * FROM table_name ORDER BY column_name ASC|DESC 8 PRATIE MANDI table_mame : name of the table. cobra column_name : name of the column according to which the data is NOT operator returns false if following condition is true. needed to be arranged. Syntax: ‘ASC : to sort the data in ascending order. SELECT * FROM table name WHERE condition! OR condition? DESC : to sort the data in descending order. OR... conditionN; Sort according to multiple columns : To sort according to table name: name of the table multiple columns, separate the names of columns by (,) operator. condition1,2,..N : first condition, second condition and so on Syntax : Example Queries SELECT * FROM tablename ORDER BY column! ASCIDESC . © To fetch all the records from Student table where Age is not equl | column? ASCIDESC t0 20. Sample table Student = SELECT * FROM Student WHERE AGE<>20; ae Output : ROLL_NO | NAME | ADDRESS | PHONE.NO | AGE er 1 RAM DELHI | caocadaax |18 =x = EOURESS 2 [RAMESH SHIMLA | xccocoaxx [18 2 R aoa = oe 3 SUJIT_| DHARAMSHALA|200000000x [20 = H_| GURGAON. 4 SURESH DELHI | XXXXXXXXxX | 18 3 SURESH DELHI 5 HARSH SOLAN | XXXXCOKKX | 19 on H SOLAN 6 PRATIK, MANDI _[XXXXXXxXxx [20 ER BY ‘The ORDE} . en pian I fetch all ata : will fete ithe BY statement in SQL is used to sort the fetched 4 * Sort according to single column: In this example We + ascendin, der or descending according to one or more column data from the table Student and sort the result in descending According to the column ROLL_NO.