Database Management System Lab Manual



Database Management System Lab ManualRoll No:____Name:__________________Sem:_______Section______-923925147955CERTIFICATECertified that this file is submitted byShri/Ku.___________________________________________________________Roll No.________a student of ________ year of the course ________________________________________________________ as a part of PRACTICAL/ORAL as prescribed by the Rashtrasant Tukadoji Maharaj Nagpur University for the subject_____________________________________ in the laboratory of ___________________________________during the academic year _________________________ and that I have instructed him/her for the said work, from time to time and I found him/her to be satisfactory progressive.And that I have accessed the said work and I am satisfied that the same is up to that standard envisaged for the course.Date:- Signature & NameSignature & Name of Subject Teacher of HODAnjuman College of Engineering and TechnologyVisionTo be a centre of excellence for developing quality technocrats with moral and social ethics, to face the global challenges for the sustainable development of society.MissionTo create conducive academic culture for learning and identifying career goals.To provide quality technical education, research opportunities and imbibe entrepreneurship skills contributing to the socio-economic growth of the Nation.To inculcate values and skills, that will empower our students towards development through technology.Vision and Mission of the DepartmentVision:To achieve excellent standards of quality education in the field of computer science and engineering, aiming towards development of ethically strong technical experts contributing to the profession in the global society.Mission:To create outcome based education environment for learning and identifying career goals. Provide latest tools in a learning ambience to enhance innovations, problem solving skills, leadership qualities team spirit and ethical responsibilities.Inculcating awareness through innovative activities in the emerging areas of technology.Program Educational Objectives (PEOs)The graduates will have a strong foundation in mathematical, scientific and engineering fundamentals necessary to formulate, solve and analyze engineering problem in their career.Graduates will be able to create and design computer support systems and impart knowledge and skills to analyze, design, test and implement various software applications.Graduates will work productively as computer science engineers towards betterment of society exhibiting ethical qualities.Program Specific Outcomes (PSOs)Foundation of mathematical concepts: To use mathematical methodologies and techniques for computing and solving problem using suitable mathematical analysis, data structures, database and algorithms as per the requirement.Foundation of Computer System: The capability and ability to interpret and understand the fundamental concepts and methodology of computer systems and programming. Students can understand the functionality of hardware and software aspects of computer systems, networks and security.Foundations of Software development: The ability to grasp the software development lifecycle and methodologies of software system and project development.PROGRAM: CSE DEGREE: B.ECOURSE: Database Management SystemSEMESTER: V CREDITS: 2COURSE CODE: BECSE303TCOURSE TYPE: REGULARCOURSE AREA/DOMAIN: Practical knowledge about storage, industryCONTACT HOURS: 2 hours/Week.CORRESPONDING LAB COURSE CODE : BECSE303PLAB COURSE NAME : Database Management System LabCOURSE PRE-REQUISITES:C.CODECOURSE NAMEDESCRIPTIONSEMBE3S2TAdvanced C Programming and Logic DesignBasic Concept of C ProgrammingIIIBE4S2TData Structures & Program DesignBasic Concept of Data StructureIVLAB COURSE OBJECTIVES:Students learn how to design and create a good database and use various SQL operations. To learn the fundamental concepts of SQL queries.To understand the concept of designing a database with the necessary attributes.To know the methodology of Accessing, Modifying and Updating data & information from the relational databases.COURSE OUTCOMES: Database Management SystemAfter completion of this course the students will be able -SNODESCRIPTIONBLOOM’S TAXONOMY LEVELCO.1Transform an information model into a relational database schema and to use a data definition language and/or utilities to implement the schema using a DBMS.LEVEL 5CO.2Use an SQL interface of a multi-user relational DBMS package to create, secure, populate, maintain, and query a database.LEVEL 3CO.3Formulate query, using SQL, solutions to a broad range of query and data update problems.LEVEL 5CO.4Use a desktop database package to create, populate, maintain, and query a database.LEVEL 3CO.5Demonstrate a rudimentary understanding of programmatic interfaces to a database and be able to use the basic functions of one such interface.?LEVEL 3CO.6Analyze an information storage problem and derive an information model expressed in the form.LEVEL 4Lab Instructions:Make entry in the Log Book as soon as you enter the Laboratory.All the students should sit according to their Roll Numbers.All the students are supposed to enter the terminal number in the Log Book.Do not change the terminal on which you are working.Strictly observe the instructions given by the Faculty / Lab. Instructor.Take permission before entering in the lab and keep your belongings in the racks.NO FOOD, DRINK, IN ANY FORM is allowed in the lab. TURN OFF CELL PHONES! If you need to use it, please keep it in bags.Avoid all horseplay in the laboratory.?Do not misbehave in the computer laboratory. Work quietly.Save often and keep your files organized.Don’t change settings and surf safely. Do not reboot, turn off, or move any workstation or PC.Do not load any software on any lab computer (without prior permission of Faculty and Technical Support Personnel). Only Lab Operators and Technical Support Personnel are authorized to carry out these tasks.?Do not reconfigure the cabling/equipment without prior permission.Do not play games on systems.?Turn off the machine once you are done using it. Violation of the above rules and etiquette guidelines will result in disciplinary action.?Continuous Assessment PracticalExp NoNAME OF EXPERIMENTDateSignRemark1Study of DBMS, RDBMS, ORDBMS with advantages and disadvantage.2Implement Data Definition Language (Create, Alter, Drop, Truncate, and Rename) & Data Manipulation Language (Insert, Update, and Delete).3Implement SELECT command with different clauses (Where clause, having clause, Group by clause, Order by clause).4Implement Single Row function (character, numeric, data functions).5To implement Group function (AVG, MIN, MAX, SUM).6Implement various types of SET operators (Union, Intersect, Minus).7Implement various types of integrity constraints (NOT NULL Constraint, DEFAULT Constraint, UNIQUE Constraint, PRIMARY Key, FOREIGN Key, CHECK Constraint).8Implement various types of joins (Left Join, Right Join, Outer Join, and Inner Join).9Install Hadoop on Ubuntu single node cluster.10Perform word count in hadoop in single node cluster using Map reduce.CONTENTSExp NoNAME OF EXPERIMENTPAGE NO.1Study of DBMS, RDBMS, ORDBMS with advantages and disadvantage.82Implement Data Definition Language (Create, Alter, Drop, Truncate, and Rename) & Data Manipulation Language (Insert, Update, and Delete).173Implement SELECT command with different clauses (Where clause, having clause, Group by clause, Order by clause).244Implement Single Row function (character, numeric, data functions).325To implement Group function (AVG, MIN, MAX, SUM).416Implement various types of SET operators (Union, Intersect, Minus).457Implement various types of integrity constraints (NOT NULL Constraint, DEFAULT Constraint, UNIQUE Constraint, PRIMARY Key, FOREIGN Key, CHECK Constraint).558Implement various types of joins (Left Join, Right Join, Outer Join, and Inner Join).669Install Hadoop on Ubuntu single node cluster.7410Perform word count in hadoop in single node cluster using Map reduce.90EXPERIMENT NO – 1 Aim: Study of DBMS, RDBMS, ORDBMS with advantages and disadvantage. Theory: DBMS (Database Management System)A?DBMS?is software that allows creation, definition and manipulation of database, allowing users to store, process and analyze data easily. DBMS provides us with an interface or a tool, to perform various operations like creating database, storing data in it, updating data, creating tables in the database and a lot more.DBMS also provides protection and security to the databases. It also maintains data consistency in case of multiple users.Here are some examples of popular DBMS used these days:MySqlOracleSQL ServerIBM DB2PostgreSQLAmazon SimpleDB (cloud based) etc.Characteristics of Database Management SystemA database management system has following characteristics:Data stored into Tables:?Data is never directly stored into the database. Data is stored into tables, created inside the database. DBMS also allows having relationships between tables which makes the data more meaningful and connected. You can easily understand what type of data is stored where by looking at all the tables created in a database.Reduced Redundancy:?In the modern world hard drives are very cheap, but earlier when hard drives were too expensive, unnecessary repetition of data in database was a big problem. But DBMS follows?Normalization?which divides the data in such a way that repetition is minimal.Data Consistency:?On Live data, i.e. data that is being continuously updated and added, maintaining the consistency of data can become a challenge. But DBMS handles it all by itself.Support Multiple user and Concurrent Access:?DBMS allows multiple users to work on it (update, insert, and delete data) at the same time and still manages to maintain the data consistency.Query Language:?DBMS provides users with a simple Query language, using which data can be easily fetched, inserted, deleted and updated in a database.Security:?The DBMS also takes care of the security of data, protecting the data from un-authorized access. In a typical DBMS, we can create user accounts with different access permissions, using which we can easily secure our data by restricting user access.DBMS supports?transactions, which allows us to better handle and manage data integrity in real world applications where multi-threading is extensively used.Advantages of DBMSSegregation of application program.Minimal data duplicity or data redundancy.Easy retrieval of data using the Query Language.Reduced development time and maintenance need.With Cloud Datacenters, we now have Database Management Systems capable of storing almost infinite data.Seamless integration into the application programming languages which makes it very easier to add a database to almost any application or website.Disadvantages of DBMSIt's ComplexityExcept MySQL, which is open source, licensed DBMSs are generally costly.They are large in size. RDBMS (Relational Database management System)A?Relational Database management System (RDBMS) is a database management system based on the relational model introduced by E.F Codd. In relational model, data is stored in?relations (tables) and is represented in form of?tuples (rows).RDBMS is used to manage Relational database. Relational database is a collection of organized set of tables related to each other, and from which data can be accessed easily. Relational Database is the most commonly used database these days.In relational model in which data is stored in multiple tables where tables are related to each other using primary keys and foreign keys and indexes. RDBMS uses database normalization techniques to avoid redundancy in tables. It helps to fetch data faster using SQL query. It is widely used by enterprises and software developers to store large amount of complex dataExamples: SQL server, OracleMySQLMariaDBSQLiteImportant Concept Related to RDBMS:TableIn Relational database model, a?table?is a collection of data elements organized in terms of rows and columns. A table is also considered as a convenient representation of?relations. But a table can have duplicate row of data while a true?relation?cannot have duplicate data. Table is the simplest form of data storage. Below is an example of an Employee table.IDNameAgeSalary1Adam34130002Alex28150003Stuart20180004Ross4219020TupleA single entry in a table is called a?Tuple?or?Record?or?Row. A?tuple?in a table represents a set of related data. For example, the above?Employee?table has 4 tuples/records/rows.Following is an example of single record or tuple.1Adam3413000AttributeA table consists of several records (row), each record can be broken down into several smaller parts of data known as?Attributes. The above?Employee?table consists of four attributes,?ID,?Name,?Age?and?Salary.Attribute DomainWhen an attribute is defined in a relation (table), it is defined to hold only a certain type of values, which is known as?Attribute Domain. Hence, the attribute?Name?will hold the name of employee for every tuple. If we save employee's address there, it will be violation of the Relational database model.NameAdamAlexStuart - 9/401, OC Street, AmsterdamAdvantages of RDBMSIt is easy to use. It is secured in nature. The data manipulation can be done. It limits redundancy and replication of the data. It offers better data integrity. It provides better physical data independence. It offers logical database independence i.e. data can be viewed in different ways by the different users. It provides better backup and recovery procedures. It provides multiple interfaces. Multiple users can access the database which is not possible in DBMS.Disadvantages of RDBMSSoftware is expensive. Complex software refers to expensive hardware and hence increases overall cost to avail the RDBMS service. It requires skilled human resources to implement. Certain applications are slow in processing. It is difficult to recover the lost data.ORDBMS (Object Relational Database Management Systems)An object relational database management system (ORDBMS) is a database management system with that is similar to a relational database, except that it has an object-oriented database model. This system supports objects, classes and inheritance in database schemas and query language.Object relational database management systems provide a middle ground between relational and object-oriented databases. In an ORDBMS, data is manipulated using queries in a query language. These systems bridge the gap between conceptual data modeling techniques such as entity relationship diagrams and object relational mapping using classes and inheritance. ORDBMSs also support data model extensions with custom data types and methods. This allows developers to raise the abstraction levels at which problem domains are viewed.ORDBMS ExamplesExamples of ORDBMSs include:PostgreSQL. Open source ORDBMS developed by the?PostgreSQL Global Development Group.Oracle Database?by Oracle rmix?by IBMSQL Server?by MicrosoftGreenplum Database?by?Pivotal SoftwareAdvantages of ORDBMSReusable and Sharable – able to reuse the hard-coded components. Through database servers those components can be shared among available resources.Ability of applying Objects with existing RDBMS models as it is – That is, RDBMS can be extended with Object concepts without changing the underlying models. This leads the organizations to switch over to ORDBMS concepts easily without performing bigger migration or major changes.It allows users and programmers to start using object-oriented systems in parallel.Object Relational Database Management Systems ensures large storage capacity.Supports rich data types by adding a new object-oriented layer.ScalabilityRelationships are represented explicitly, often supporting both navigational and associative access to information.Improved concurrency - concurrent users can safely query the same data.Support for Composite data types - data is bundled with its metadata.Improved integrity - ability to reject bad data before it is stored in an ORDBMS.Database extensibility - easy addition of data types and operations.Uniform treatment of data items - the SQL interface can perform complex queries based on any of these data items, e.g., metadata as well as data; hence there is less need for custom programming by users.Custom data access methods - e.g., R-tree indexes.Point-in-time recovery of data is possible.Built-in complex SQL functions can be provided for data operations - e.g., aggregating, slicing, subsetting, reprojecting, etc.Disadvantages of ORDBMSComplexityIncreased costUnclear if the ORDBMS will actually combine relationships and encapsulated objects to correctly and completely mirror the ‘real world’.Conclusion: Thus we, have studied DBMS, RDBMS and ORDBMS with its advantages and disadvantages successfully.Viva Voce QuestionDefine Database? What are DBMS and RDBMS?_________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What do you understand by Data Redundancy?_________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the difference between DBMS and RDBMS?_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO – 2 Aim: Implement Data Definition Language (Create, Alter, Drop, Truncate, and Rename) & Data Manipulation Language (Insert, Update, and Delete).Theory: DDL-(Data Definition Language) The data definition language is used to create an object, alter the structure of an object and also drop already created object. The Data Definition Languages used for table definition can be classified into following: CREATE TABLE - to create objects in the databaseALTER TABLE - alters the structure of the databaseDROP TABLE- delete objects from the databaseCOMMENT - add comments to the data dictionaryRENAME - rename an objectBefore creating table follow these steps to create table in MySql.mysql => mysql -u root -p show databases; create database sectionB34; use sectionB34; CREATE TABLEIt defines each column of the table uniquely. Each column has minimum of three attributes, a name, data type and size.Here, a few items need explanation: Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error. Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field. Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.Syntax:Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));Ex:create table stud(RollNo int, Name varchar(20), DOB date);SHOW TABLESSHOW TABLES?lists the non-TEMPORARY?tables in a given database.Syntax: Show TablesDESCRIBE TABLE: The?DESCRIBE?and?EXPLAIN?statements are synonyms, used either to obtain information about table structure or query execution plans.Syntax:?DESCRIBE <TABLE NAME>; Ex: describe stud;MODIFYING THE STRUCTURE OF TABLES USING ALTER TABLE STATEMENTALTER is used to modify existing database data structures (database, table).a) Add new columnsSyntax: Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size));Ex: alter table stud add enno varchar(20); alter table stud add email varchar(20) after name;b) Dropping a column from a tableSyntax: Alter table <tablename> drop <column-name>;Ex: alter table stud drop email; c) Modifying existing columns.Syntax: Alter table <tablename> modify (<column name><newdatatype>(<newsize>));Ex: alter table stud modify email varchar(30);d) Renaming the tables RENAME command is used to rename SQL tableSyntax: Alter table <old table name> RENAME to <new table name>;Ex: Alter table stud rename to student;Destroying tablesDROP statement allows you to remove database, table, index or stored procedure.Syntax: Drop <table name>;Ex: drop stud;COMMENT?- add comments to the data dictionary?DML- (Data Manipulation Language)DML- Data Manipulation Language (DML) statements are used for managing data within schema objects DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. DML allows adding / modifying / deleting data itself.DML is used to manipulate with the existing data in the database objects (insert, select, update, delete).DML Commands: INSERTSELECTUPDATEDELETEINSERT INTO: To insert data into a MySQL table, you would need to use the SQL INSERT INTO command. To insert string data types, it is required to keep all the values into double or single quotes. For example – "value".Syntax: INSERT INTO table_name (field1, field2,……fieldN ) VALUES ( value1, value2,...valueN )EX: insert into stud (RollNo, Name, DOB, enno) values (5,'Naaz','1998-01-10',115); SELECT: The SQL SELECT command is used to fetch data from the MySQL database.You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command. You can fetch one or more fields in a single SELECT command.You can specify star (*) in place of fields. In this case, SELECT will return all the fields. You can specify any condition using the WHERE clause.Syntax: SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE Clause]EX: select * from stud; UPDATE:There may be a requirement where the existing data in a MySQL table needs to be modified. You can do so by using the SQL UPDATE command. This will modify any field value of any MySQL table.You can update one or more field altogether. You can specify any condition using the WHERE clause.You can update the values in a single table at a time.The WHERE clause is very useful when you want to update the selected rows in a table.Syntax: UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]EX: update stud set Name='Aisha' where RollNo='2';DELETE:If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM.If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table. You can specify any condition using the WHERE clause.You can delete records in a single table at a time.The WHERE clause is very useful when you want to delete selected rows in a tableSyntax: DELETE FROM table_name [WHERE Clause]EX: Delete from stud where Rollno=10;Conclusion: Thus we, have studied Data Definition Language (Create, Alter, Drop, Truncate, and Rename) & Data Manipulation Language (Insert, Update, and Delete) Successfully.Viva Voce QuestionWhat is SQL? What are Tables in SQL?______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Differentiate between ‘DELETE’, ‘TRUNCATE’ and ‘DROP’ commands.______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What are different types of statements supported by SQL? ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO – 3Aim: Implement SELECT command with different clauses (Where clause, having clause, Group by clause, Order by clause).Theory: SELECT: The SQL SELECT command is used to fetch data from the MySQL database.You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command. You can fetch one or more fields in a single SELECT command.You can specify star (*) in place of fields. In this case, SELECT will return all the fields. You can specify any condition using the WHERE clause.Syntax: SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE Clause]EX: select * from stud; SELECT STATEMENT WITH WHERE CLAUSEWe have seen the SQL SELECT command to fetch data from a MySQL table. We can use a conditional clause called the WHERE Clause to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.Syntax: SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2..... EX: Select name, age from stud where rollno=2;You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command. You can specify any condition using the WHERE clause.You can specify more than one condition using the AND or the OR operators.A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition. The?WHERE?clause works like an?if condition?in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.Here is the list of operators, which can be used with the?WHERE?clause.Assume field A holds 10 and field B holds 20, then –OperatorDescriptionExample=Checks if the values of the two operands are equal or not, if yes, then the condition becomes true.(A = B) is not true.!=Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true.(A != B) is true.>Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.(A > B) is not true.<Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true.(A < B) is true.>=Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.(A >= B) is not true.<=Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.(A <= B) is true.BETWEEN OPERATOREx: select * from emp_master where salary between 5000 and 8000;The above select statement will display only those rows where salary of employee is between 5000 and 8000.IN Operator: The in operator can be used to select rows that match one of the values in a list.EX: Select * from emp where deptno in (10, 30);The above query will retrieve only those rows where deptno is either in 10 or 30.Logical Operators: Logical operators are used to combine the results of two conditions to produce a single result. The logical operators are AND, NOT and OR.AND Operator: The Oracle engine will process all rows in a table and display the result only when all the conditions specified using the AND operator are satisfied.EX: select * from emp_master where salary > 5000 and comm < 750;The select statement will return only those rows where salary is greater than 5000 and comm is less than 750. If both the conditions are true then only it will retrieve rows.OR Operator:The Oracle engine will process all rows in a table and display the result only when any of the conditions specified using the OR operators are satisfied. EX: select * from emp_master where salary > 5000 or comm < 750;This select statement will check either salary is greater than 5000 or comm is less than 750. I.e. it will return all the records either of any one condition returns true.NOT Operator:The Oracle engine will process all rows in a table and display the result only when none of the conditions specified using the NOT operator are satisfied.EX: select * from emp_master where not salary = 10000;This select statement will return all the records where salary is NOT equal to 10000.LIKE Operator:Like operator is used to search character pattern, we need not know the exact character value. The like operator is used with special character % and _ (underscore). Syntax: SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue' You can specify any condition using the WHERE clause. You can use the LIKE clause along with the WHERE clause.You can use the LIKE clause in place of the equal to sign.When LIKE is used along with % sign then it will work like a Meta character search.You can specify more than one condition using AND or OR operators.A WHERE...LIKE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.EX: select * from emp_master where job like ‘M%’;The above select statement will display only those rows where job is starts with ‘M’ followed by any number of any characters. % sign is used to refer number of characters (it similar to * asterisk wildcard in DOS), while _ (underscore) is used to refer single character.EX: Select * from emp_master where job like ‘_lerk’;In above query, it will display only those rows where job is start with any single character but ends with ‘clerk’.DISTINCT CLAUSETo prevent the selection of distinct rows, we can include distinct clause with select command. The following command will exclude duplicate empno.EX: select distinct deptno from emp_master;Order by ClauseOrder by clause is used to arrange rows in either ascending or descending order. The order by clause can also be used to arrange multiple columns.Syntax: SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]] You can sort the returned result on any field, if that field is being listed out.You can sort the result on more than one field.You can use the keyword ASC or DESC to get result in ascending or descending order. By default, it's the ascending order. You can use the WHERE...LIKE clause in the usual way to put a condition.If you want to view salary in ascending order the following command can performed:EX: Select empno,ename,salary from emp_master order by salary;If you have not specified any order by default it will consider ascending order and salary will be displayed in ascending order. To retrieve data in descending order the desc keyword is used after order by clause.EX: Select empno,ename,salary from emp_master order by salary desc; Group by ClauseGroup by clause is used with group functions only. Normally group functions returns only one row. But group by clause will group on that column. The group by clause tells Oracle to group rows based on distinct values for specified columns, i.e. it creates a data set, containing several sets of records grouped together based on a condition.Syntax: SELECT statements... GROUP BY column_name1[,column_name2,...] ;EX: select deptno,count(*) from emp_master group by deptno; Having ClauseThe having clause is used to satisfy certain conditions on rows, retrieved by using group by clause.Having clause should be proceeding by a group by clause. Having clause further filters the rows return by group by clause.Syntax: SELECT statements... GROUP BY column_name1[,column_name2,...] [HAVING condition];EX: select deptno,count(*) from emp_master group by deptno having Deptno =1;Conclusion: Thus we, have Implement SELECT command with different clauses (Where clause, having clause, Group by clause, Order by clause) successfully.Viva Voce QuestionExplain the difference between Group by and Having Clause?______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Explain where statement with different clauses.______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Describe Like operator? ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO – 4Aim: Implement Single Row function (character, numeric, date functions).Theory: Single Row Functions (Scalar Functions):Functions that act on only one value at a time are called as Single Row Functions. A Single Row function returns one result for every row of a queried table or view. Single row functions can be character functions, numeric functions, date functions, and conversion functions. Note that these functions are used to manipulate data items. These functions require one or more input arguments and operate on each row, thereby returning one output value for each row. Argument can be a column, literal or an expression. Single row functions can be used in SELECT statement, WHERE and ORDER BY clause. Single Row functions can be further grouped together by the data type of their arguments and return values. Functions can be classified corresponding to different data types as:String Functions: Work for String Data typeNumeric Functions: Work for number Data typeConversion Functions: Work for conversion of one data type to anotherDate Functions: Work for Date Data type 36Case Conversion functions - Accepts character input and returns a character value. Functions under the category are UPPER and LOWER.UPPER function converts a string to upper case.LOWER function converts a string to lower case.String functions - Accepts character input and returns number or character value. Functions under the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD and TRIM.CONCAT function concatenates two string values.LENGTH function returns the length of the input string.SUBSTR function returns a portion of a string from a given start point to an end point.LPAD and RPAD functions pad the given string upto a specific length with a given character.TRIM function trims the string input from the start or end.Date functions - Date arithmetic operations return date or numeric values. Functions under the category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC.MONTHS_BETWEEN function returns the count of months between the two dates.ADD_MONTHS function adds 'n' number of months to an input date.NEXT_DAY function returns the next day of the date specified.LAST_DAY function returns last day of the month of the input date.ROUND and TRUNC functions are used to round and truncate the date value.Number functions - Accepts numeric input and returns numeric values. Functions under the category are ROUND, TRUNC, and MOD.ROUND and TRUNC functions are used to round and truncate the number value.MOD is used to return the remainder of the division operation between two numbers.“The Dual Table”Dual is a small MySql worktable, which consists of only one row and one column, and contains the value x in that column. Besides arithmetic calculations, it also supports date retrieval and it’s formatting.SQL> select 2*2 from dual;2*24String Functions:String functions accept string input and return either string or number values.ASCII: Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII () works for characters with numeric values from 0 to 255.Syntax: ASCII(string);Example: SELECT ASCII('2') from dual;Output: 50 Syntax: SELECT ASCII('dx') from dual;Output: 100 BIN: Returns a string representation of the binary value of N, where N is a long long (BIGINT) number. This is equivalent to CONV (N, 10, 2). Returns NULL if N is NULL.Syntax: BIN (Number)Example:SELECT BIN(12) from dual;Output: 1100 Lower: This String function will convert input string in to lower case.Syntax: Lower (string)Example: select lower (‘AZURE’) from dual;Output: azureUpper: This string function will convert input string in to upper case.Syntax:Upper (string)Example:select upper (‘azure’) from dual;Output: AZURELTrim (Left trim): Ltrim function accepts two string parameters; it will fetch only those set of characters from the first string from the left side of the first string, and displays only those characters which are not present in second string. If same set of characters are not found in first string it will display whole stringSyntax: Ltrim(string,set)Example: select ltrim(‘azuretech’,’azure’) from dual;Output: techRtrim (Right Trim): Rtrim function accepts two string parameters; it will fetch only those characters from the first string, which is present in set of characters in second string from the right side of the first string.Syntax:Rtrim(string,set)Example:select rtrim(‘azuretrim’,’trim’) from dual;Output: azureSubstr: Substring fetches out a piece of the string beginning at start and going for count characters, if count is not specified, the string is fetched from start and goes till end of the string.Syntax:Substr(string, starts [, count])Example:select substr(‘azuretechnology’,4,6) from dual;Output: retechChr: Character function except character input and returns either character or number values. The first among character function is chr. This returns the character value of given number within braces.Syntax: Chr(number)Example:select chr(65) from dual;Output: ALpad (Left Pad): This function takes three arguments. The first argument is character string, which has to be displayed with the left padding. Second is a number, which indicates total length of return value and third is the string with which left padding has to be done when required.Syntax:Lpad(String,length,pattern)Example:select lpad(‘Welcome’,15,’*’) from dual;Output: ********WelcomeRpad (Right Pad): Rpad does exact opposite then Lpad function.Syntax:Lpad(String,length,pattern)Example:select rpad(‘Welcome’,15,’*’) from dual;Output: Welcome********Length: When the length function is used in a query. It returns length of the input string.Syntax:Length(string)Example:select length(‘auzre’) from dual;Output: 5 Concatenation ( || ) Operator: This operator is used to merge two or more strings.Syntax:Concat(string1,string2)Example: select concat(‘Azure’,’ Technology’) from dual;Output: Azure TechnologyExample:select ‘ename is ‘||ename from emp_master;Output: ‘ENAME IS’||ENAME————————-ename is Allenename is Kingename is Martinename is TanmayNumeric Functions:Abs (Absolute): Abs() function always returns positive number.Syntax: Abs (Negative Number)Example: select Abs (-10) from dual;Output: 10Ceil: This function will return ceiling value of input number. i.e. if you enter 20.10 it will return 21 and if you enter 20.95 then also it will return 21. so if there is any decimal value it will add value by one and remove decimal value.Syntax:Ceil (Number)Example:select Ceil (23.77) from dual;Output: 24Floor: This function does exactly opposite of the ceil function.Syntax: Floor (Number)Example:select Floor (45.3) from dual;Output: 45Power: This function will return power of raise value of given number.Syntax:Power (Number, Raise)Example:Select power (5, 2) from dual;Output: 25Mod: The function gives the remainder of a value divided by another value.Syntax: Mod (Number, Division Value)Example:select Mod (10, 3) from dual;Output: 1Date Function:CURDATE(): Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.Example:SELECT CURDATE ();Output: 1997-12-15 CURTIME (): Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.Example:SELECT CURTIME ();Output:23:50:26 DAYNAME (date): Returns the name of the weekday for date.Example:SELECT DAYNAME ('1998-02-05');Output:Thursday DAYOFMONTH (date): Returns the day of the month for date, in the range 0 to 31.Example: SELECT DAYOFMONTH ('1998-02-03');Output:3 DAYOFWEEK (date): Returns the weekday index for date (1 = Sunday, 2 = Monday…, 7 = Saturday). These index values correspond to the ODBC standard.Example: SELECT DAYOFWEEK ('1998-02-03');Output:3 DAYOFYEAR (date): Returns the day of the year for date, in the range 1 to 366.Example:SELECT DAYOFYEAR ('1998-02-03');Output:34 MONTH (date): Returns the month for date, in the range 0 to 12.Example: SELECT MONTH ('1998-02-03')Output:2 MONTHNAME (date): Returns the full name of the month for date.Example:SELECT MONTHNAME ('1998-02-05');Output:February Conclusion: Thus we, have Implement Single Row function (character, numeric, date functions) successfully.Viva Voce QuestionWhat is Scalar function in MySql?______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Differentiate between LPAD and RPAD commands.______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What are different types Date functions? ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO – 5Aim: To implement Group function (AVG, MIN, MAX, SUM).Theory: Group Functions:These are the functions in MySQL that performs some calculation on a set of values and then returns a single value. A group functions returns a result based on a group of rows. Some of these are just purely mathematical functions. List of aggregate functions COUNT functionMIN functionMAX functionAVG functionSUM functionAvg (Average): This function will return the average of values of the column specified in the argument of the column.Syntax:SELECT AVG (column name) FROM table name;Example:select avg(comm) from emp_master;AVG with WHERE clauseSyntax: SELECT AVG (column name) FROM table name WHERE condition;Min (Minimum): The function will give the least of all values of the column present in the argument.Syntax:SELECT MIN (column name) FROM table name;Example:Select min (salary) from emp_master;MIN with WHERE clauseSyntax:SELECT MIN (column name) FROM table name WHERE condition;Max (Maximum): To perform an operation, which gives the maximum of a set of values the max, function can be made use of.Syntax:SELECT MAX (column name) FROM table name;Example: select max (salary) from emp_master;MAX with WHERE clauseSyntax: SELECT MAX (column name) FROM table name WHERE condition;This query will return the maximum value of the column specified as the argument.Sum: The sum function can be used to obtain the sum of a range of values of a record set.Syntax:SELECT SUM (column name) FROM table name;Example:Select sum (comm) from emp_master;SUM with WHERE clauseSyntax:SELECT SUM (column name) FROM table name WHERE condition;Count: This function is used to count number rows. It can take three different arguments, which mentioned below.Count (Column name): It counts the number of values present in the column without including nulls.Syntax: SELECT COUNT (column name) FROM table name;Example:select count (comm) from emp_master;Count (*): This will count all the rows, including duplicates and nulls.Syntax: Select Count (*) FROM table name;Example: Select count (*) from emp_master;Count (distinct column name): It is similar to count (column name) but eliminates duplicate values while counting.Syntax: Select Count (distinct column name) FROM table name;Example:Select count (distinct deptno) from emp_master;Conclusion: Thus we, have implement Group function (AVG, MIN, MAX, SUM).successfully.Viva Voce QuestionWhat is aggregate function in MySql?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the usage of aggregate functions?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO – 6Aim: Implement various types of SET operators (Union, Intersect, Minus).Theory: SET Operators:SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions.In this tutorial, we will cover 4 different types of SET operations, along with example:UNIONUNION ALLINTERSECTMINUSUNION?operatorMySQL?UNION?operator allows you to combine two or more result sets of queries into a single result set. The following illustrates the syntax of the?UNION?operator:Syntax: SELECT column_list UNION [DISTINCT | ALL] SELECT column listUNION [DISTINCT | ALL]SELECT column list...To combine result set of two or more queries using the?UNION?operator, there are the basic rules that you must follow:First, the number and the orders of columns that appear?in all?SELECT?statements must be the same.Second, the?data types?of columns must be the same or convertible.By default, the?UNION?operator removes?duplicate rows?even if you don’t specify?the?DISTINCT?operator explicitly.Let’s see the following sample tables:?t1?and?t2:Example:DROP TABLE IF EXISTS t1;DROP TABLE IF EXISTS t2;CREATE TABLE t1 ( id INT PRIMARY KEY); CREATE TABLE t2 ( id INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3);INSERT INTO t2 VALUES (2),(3),(4);The following statement combines result sets returned from?t1?and?t2?tables:SELECT id FROM t1 UNION SELECT id FROM t2;The final result set contains the distinct values from separate result sets returned by the queries:+----+| id |+----+| 1 || 2 || 3 || 4 |+----+Because the rows with value 2 and 3 are duplicates, the?UNION?operator removed it and kept only distinct ones.The following Venn diagram illustrates the union of two result sets that come from?t1?and?t2?tables:As you can see, the duplicates appear in the combined result set because of the?UNION ALL?operation.If you use the?UNION ALL?explicitly, the duplicate rows, if available, remain in the result. Because?UNION ALL?does not need to handle duplicates, it performs faster than?UNION DISTINCT.SELECT id FROM t1 UNION ALL SELECT id FROM t2;+----+| id |+----+| 1 || 2 || 3 || 2 || 3 || 4 |UNION?vs.?JOINA?JOIN?combines result sets horizontally; a?UNION?appends result set vertically. The following picture illustrates the difference between?UNION?and?JOIN:INTERSECT operatorThe?INTERSECT?operator is a set operator that returns only distinct rows of two queries or more queries.The following illustrates the syntax of the?INTERSECT?operator.Syntax: (SELECT column _list FROM table_1)INTERSECT(SELECT column _list FROM table_2);The?INTERSECT?operator compares the result of two queries and returns the distinct rows that are output by both left and right queries.To use the?INTERSECT?operator for two queries, the following rules are applied:The order and the number of columns must be the same.The data types of the corresponding columns must be compatible.The following diagram illustrates the?INTERSECT?operator.The left query?produces a result set of (1,2,3).The right query returns a result set of (2,3,4).The?INTERSECT?operator returns the distinct rows of both result sets which include (2,3). Unlike the?UNION?operator, the?INTERSECT?operator returns the intersection between two circles.Note that SQL standard has three set operators that?include?UNION,?INTERSECT, and?MINUS.MySQL?INTERSECT?simulationUnfortunately, MySQL does not support the?INTERSECT?operator. However, you can simulate the?INTERSECT?operator.Let’s create some sample data for the demonstration.The following statements?create?table’s?t1?and?t2, and then?insert data?into both tables.Example: CREATE TABLE t1 ( id INT PRIMARY KEY); CREATE TABLE t2 LIKE t1; INSERT INTO t1 (id) VALUES (1), (2), (3); INSERT INTO t2 (id) VALUES (2), (3), (4);The following query returns rows from the?t1?table.SELECT id FROM t1;id----123The following query returns the rows from the?t2?table:SELECT id FROM t2;Id---234Simulate MySQL INTERSECT operator using DISTINCT operator and INNER JOIN clause.The following statement uses?DISTINCT?operator and?INNER JOIN?clause to return the distinct rows in both tables:Example: SELECT DISTINCT id FROM t1 INNER JOIN t2 USING (id);id----23How it works.The?INNER JOIN?clause returns rows from both left and right tables.The?DISTINCT?operator removes the duplicate rows.Simulate MySQL INTERSECT operator using IN operator and sub queryThe following statement uses the?IN?operator and a?sub query?to return the intersection of the two result sets.Example: SELECT DISTINCT idFROM t1WHERE id IN (SELECT id FROM t2);id----23How it works.The sub query returns the first result set.The outer query uses the IN operator to select only values?that are in the first result set. The?DISTINCT?operator ensures that only distinct values are selected.MINUS OPERATORMINUS?is one of three set operations in the SQL standard that includes?UNION,?INTERSECT, and?MINUS.MINUS?compares results of two queries and returns distinct rows from the first query that isn’t output by the second query.The following illustrates the syntax of the?MINUS?operator:Syntax:SELECT column_list_1 FROM table_1MINUS SELECT columns_list_2 FROM table_2;The basic rules for a query that uses?MINUS?operator are the following:The number and order of columns in both?column_list_1?and?column_list_2?must be the same.The data types of the corresponding columns in both queries must be compatible.Suppose we have two tables’?t1?and?t2?with the following structure and data.Example: CREATE TABLE t1 ( id INT PRIMARY KEY); CREATE TABLE t2 ( id INT PRIMARY KEY); INSERT INTO t1 VALUES (1), (2), (3);INSERT INTO t2 VALUES (2), (3), (4);The following query returns distinct values from the query of the?t1?table that are not found on the result of the query of the?t2?table.SELECT id FROM t1MINUSSELECT id FROM t2; The following Venn diagram illustrates the?MINUS?operator:Unfortunately, MySQL does not support?MINUS?operator. However, you can use the?MySQL join?to simulate it.To emulate the?MINUS?of two queries, you use the following syntax:SELECT column_list FROM table_1 LEFT JOIN table_2 ON join_predicateWHERE table_2.id IS NULL;For example, the following query uses the?LEFT JOIN?clause to return the same result as the?MINUS operator:SELECT idFROM t1 LEFT JOIN t2 USING (id)WHERE t2.id IS NULL; In this tutorial, you have learned about the SQL MINUS operator and how to implement MySQL MINUS operator using?LEFT JOIN?clause.Conclusion: Thus we, have implement various types of SET operators (Union, Intersect, Minus) successfully.Viva Voce QuestionWhat is Set Operation in MySql?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the difference between Union and Intersection operation?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the difference between SET and JOIN operation?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO- 7Aim: Implement various types of integrity constraints (NOT NULL Constraint, DEFAULT Constraint, UNIQUE Constraint, PRIMARY Key, FOREIGN Key, CHECK Constraint).Theory: Integrity Constraints: MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database.MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.MySQL CONSTRAINTS can be classified into two types - column level and table level.The column level constraints can apply only to one column where as table level constraints are applied to the entire table.MySQL CONSTRAINT is declared at the time of creating a table.MySQL CONSTRAINTs are:NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECKDEFAULTCONSTRAINTDESCRIPTIONNOT NULLIn MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.UNIQUEThe UNIQUE constraint in MySQL does not allow inserting a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.PRIMARY KEYA PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.FOREIGN KEYA FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.CHECKA CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.DEFAULTIn a MySQL table, each column must contain a value (including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.NOT NULL ConstraintBy default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such a constraint on this column specifying that NULL is now not allowed for that column.A NULL is not the same as any data; rather, it represents unknown data.Examplethe following SQL query creates a new table called CUSTOMERS and adds five columns, three of which, are ID NAME and AGE, In this we specify not to accept NULLs ?CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));If CUSTOMERS table has already been created, then to add a NOT NULL constraint to the SALARY column in Oracle and MySQL, you would write a query like the one that is shown in the following code block.ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) NOT NULL;Default ConstraintThe DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.ExampleThe following SQL creates a new table called CUSTOMERS and adds five columns. Here, the SALARY column is set to 5000.00 by default, so in case the INSERT INTO statement does not provide a value for this column, then by default this column would be set to 5000.00.CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID));If the CUSTOMERS table has already been created, then to add a DEFAULT constraint to the SALARY column, you would write a query like the one which is shown in the code block below.ALTER TABLE CUSTOMERSMODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00; Drop Default ConstraintTo drop a DEFAULT constraint, use the following SQL query.ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT;UNIQUE ConstraintThe UNIQUE Constraint prevents two records from having identical values in a column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having an identical age.Example The following SQL query creates a new table called CUSTOMERS and adds five columns. Here, the AGE column is set to UNIQUE, so that you cannot have two records with the same age.CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));If the CUSTOMERS table has already been created, then to add a UNIQUE constraint to the AGE column. You would write a statement like the query that is given in the code block below.ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE;You can also use the following syntax, which supports naming the constraint in multiple columns as well.ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);DROP a UNIQUE ConstraintTo drop a UNIQUE constraint, use the following SQL query.ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;If you are using MySQL, then you can use the following syntax ?ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;Primary key ConstraintA primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).Note?? you would use these concepts while creating database tables.Create Primary KeySyntax to define the ID attribute as a primary key in a CUSTOMERS table.CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));To create a PRIMARY KEY constraint on the "ID" column when the CUSTOMERS table already exists, use the following SQL syntax ?ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);NOTE?? If you use the ALTER TABLE statement to add a primary key, the primary key column(s) should have already been declared to not contain NULL values (when the table was first created).For defining a PRIMARY KEY constraint on multiple columns, use the SQL syntax given below.CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME));To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL syntax.ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);Delete Primary KeyYou can clear the primary key constraints from the table with the syntax given below.ALTER TABLE CUSTOMERS DROP PRIMARY KEY;Foreign key ConstraintA foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).ExampleConsider the structure of the following two tables.CUSTOMERS tableCREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));ORDERS tableCREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS (ID), AMOUNT double, PRIMARY KEY (ID));If the ORDERS table has already been created and the foreign key has not yet been set, the use the syntax for specifying a foreign key by altering a table.ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);DROP a FOREIGN KEY ConstraintTo drop a FOREIGN KEY constraint, use the following SQL syntax.ALTER TABLE ORDERS DROP FOREIGN KEY;CHECK ConstraintThe CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered the table.ExampleThe following program creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you cannot have any CUSTOMER who is below 18 years.CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18), ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));If the CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you would write a statement like the one given below.ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );You can also use the following syntax, which supports naming the constraint in multiple columns as well ?ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18);DROP a CHECK ConstraintTo drop a CHECK constraint, use the following SQL syntax. This syntax does not work with MySQL.ALTER TABLE CUSTOMERS DROP CONSTRAINT myCheckConstraint;Conclusion: Thus, we have successfully Implemented various types of integrity constraints (NOT NULL Constraint, DEFAULT Constraint, UNIQUE Constraint, PRIMARY Key, FOREIGN Key, and CHECK Constraint).Viva Voce QuestionExplain different constraints to maintain data integrity in SQL Server?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the difference between primary key and unique key constraints?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the Referential Integrity and foreign keys?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO- 8Aim: Implement various types of joins (Left Join, Right Join, Outer Join, and Inner Join).Theory: JOINS: We understand the benefits of taking a Cartesian product of two relations, which gives us all the possible tuples that are paired together. But it might not be feasible for us in certain cases to take a Cartesian product where we encounter huge relations with thousands of tuples having a considerable large number of attributes.Join?is a combination of a Cartesian product followed by a selection process. A Join operation pairs two tuples from different relations, if and only if a given join condition is satisfied.We will briefly describe various join types in the following sections.Theta (θ) JoinTheta join combines tuples from different relations provided they satisfy the theta condition. The join condition is denoted by the symbol?θ.NotationR1 ?θ R2R1 and R2 are relations having attributes (A1, A2, .., An) and (B1, B2,.. ,Bn) such that the attributes don’t have anything in common, that is R1 ∩ R2 = Φ.Theta join can use all kinds of comparison operators.StudentSIDNameStd101Alex10102Maria11SubjectsClassSubject10Math10English11Music11SportsStudent_Detail ?STUDENT ?Student.Std = Subject.Class SUBJECTStudent_detailSIDNameStdClassSubject101Alex1010Math101Alex1010English102Maria1111Music102Maria1111SportsEquijoinWhen Theta join uses only?equality?comparison operator, it is said to be equijoin. The above example corresponds to equijoin.Natural Join (?)Natural join does not use any comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain.Natural join acts on those matching attributes where the values of attributes in both the relations are same.CoursesCIDCourseDeptCS01DatabaseCSME01MechanicsMEEE01ElectronicsEEHoDDeptHeadCSAlexMEMayaEEMiraCourses ? HoDDeptCIDCourseHeadCSCS01DatabaseAlexMEME01MechanicsMayaEEEE01ElectronicsMiraOuter JoinsTheta Join, Equijoin, and Natural Join are called inner joins. An inner join includes only those tuples with matching attributes and the rest are discarded in the resulting relation. Therefore, we need to use outer joins to include all the tuples from the participating relations in the resulting relation. There are three kinds of outer joins ? left outer join, right outer join, and full outer join.Left Outer Join(R??S)All the tuples from the Left relation, R, are included in the resulting relation. If there are tuples in R without any matching tuple in the Right relation S, then the S-attributes of the resulting relation are made NULL.LeftAB100Database101Mechanics102ElectronicsRightAB100Alex102Maya104MiraCourses??HoDABCD100Database100Alex101Mechanics------102Electronics102MayaRight Outer Join: ( R??S )All the tuples from the Right relation, S, are included in the resulting relation. If there are tuples in S without any matching tuple in R, then the R-attributes of resulting relation are made NULL.Courses??HoDABCD100Database100Alex102Electronics102Maya------104MiraFull Outer Join: ( R??S)All the tuples from both participating relations are included in the resulting relation. If there are no matching tuples for both relations, their respective unmatched attributes are made NULL.Courses??HoDABCD100Database100Alex101Mechanics------102Electronics102Maya------104MiraConclusion: Thus, we have successfully implemented various types of JOINS.Viva Voce QuestionWhat are the different types of SQL?JOIN?clauses, and how are they used?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the difference between Inner Join and Outer Join?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What is the difference between left Join and Right Join?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO- 9Aim: Install Hadoop on Ubuntu single node cluster.Theory: IntroductionApache Hadoop is an open source framework which can develop open-source software for scalable, reliable and distributed computing. Hadoop allows distributed processing of large data sets across clusters (Collection of computers) using simple programming models such as MapReduce. The main advantage of Hadoop is that it is entirely scalable. It is designed to scale upwards from a single server to hundreds and thousands of machines. Also, each machine offers local storage as well as communication. Amazing, isn’t it?To err is human, to forgive is divine.?We all wish we can mitigate the effects of our faults. But that’s not the case for a network running with thousands of machines. It is something which they can’t afford. To vanquish that issue, Hadoop is designed to detect and handle failures at any cluster efficiently.Latest Hadoop 3.0.0 comprises these main modules:Hadoop CommonHadoop Distributed File SystemHadoop YARNHadoop MapReduceLet’s walk through hadoop installation on your Linux distribution and start running your first MapReduce program in Hadoop. All you need is a computer with Linux installed, internet connection of course and patience. Yes, patience. The operating system I am using is Ubuntu 17.10 and distribution of Hadoop is 3.0.0 because it is the latest stable release. By the end of the post you’ll know how to install hadoop on ubuntu.Hadoop Installation StepsSTEP 1 – Separate LoginHit CTRL+ALT+T to get started. We will install Hadoop from the terminal. For new Linux users, things might get confusing while installing different programs and managing them from the same login. If you are one of them, we have a solution. Let’s create a new dedicated Hadoop user. Whenever you want to use Hadoop, just use the separate login. Simple.$ sudo addgroup hadoop$ sudo adduser –ingroup hadoop hduserNote: You do not have to write passwords or names. Just hit enter and press ‘y’ at the end.Add Hadoop user to sudo group (Basically, grant it all permissions)$ sudo adduser hduser sudoSTEP 2 – Getting Environment ReadyIn order to run perfectly, Hadoop needs basic two things in Ubuntu environment. First is?Java 1.6 or higher?because Hadoop’s distributed processing and storage are written in Java. Second is?ssh(Secure Shell)?for the security of the communication between the nodes of the cluster.It is always better to update the package list before installing anything new. Let’s get started. Update package list,$ sudo apt-get update2.1 Install JAVANext, we will install the default java development kit “OpenJDK”$ sudo apt-get install default-jdkOnce it is installed, check the java version. I have 1.8 installed which is higher than the required 1.6 so we are good to go.$ java -version2.2 Install SSH$ sudo apt-get install sshPassword less entry for local host using SSH$ su hduser$ sudo ssh-keygen -t rsaNote: When ask for file name or location, leave it blank.$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys$ chmod 0600 ~/.ssh/authorized_keysCheck if ssh works,$ ssh localhostOnce we are logged in localhost, exit from this session using following command.$ exitSTEP 3 – Install Hadoop on UbuntuThe environment is now ready to install hadoop on ubuntu. ?Moreover, the procedure to install hadoop on linux (for the newer versions) will remain same. Only the folder name changes from hadoop-3.0.0 to hadoop-x.y.z3.1 Download Hadoop$ wget it$ tar xvzf hadoop-3.0.0.tar.gz3.2 Hadoop ConfigurationMake a directory called hadoop and move the folder ‘hadoop-3.0.0’ to this directory$ sudo mkdir -p /usr/local/hadoop$ cd hadoop-3.0.0/$ sudo mv * /usr/local/hadoop$ sudo chown -R hduser:hadoop /usr/local/hadoopSTEP 4 – Setting up Configuration filesWe will change content of following files in order to complete hadoop installation.~/.bashrchadoop-env.shcore-site.xmlhdfs-site.xmlyarn-site.xml4.1 ~/.bashrcIf you don’t know the path where java is installed, first run the following command to locate it$update-alternatives –config javaNow open the ~/.bashrc file$sudo nano ~/.bashrcNote: I have used ‘nano’ editor, you can use a different one. No issues.Now once the file is opened, append the following code at the end of file,#HADOOP VARIABLES STARTexport JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64export HADOOP_HOME=/usr/local/hadoopexport PATH=$PATH:$HADOOP_HOME/binexport PATH=$PATH:$HADOOP_HOME/sbinexport HADOOP_MAPRED_HOME=$HADOOP_HOMEexport HADOOP_COMMON_HOME=$HADOOP_HOMEexport HADOOP_HDFS_HOME=$HADOOP_HOMEexport YARN_HOME=$HADOOP_HOMEexport HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/nativeexport HADOOP_OPTS=”-Djava.library.path=$HADOOP_HOME/lib”#HADOOP VARIABLES ENDPress CTRL+O to save and CTRL+X to exit from that window.Update .bashrc file to apply changes$source ~/.bashrc4.2 hadoop-env.shWe need to tell Hadoop the path where java is installed. That’s what we will do in this file, specify the path for JAVA_HOME variable.Open the file,$sudo nano /usr/local/hadoop/etc/hadoop/hadoop-env.shNow, the first variable in file will be JAVA_HOME variable, change the value of that variable toexport JAVA_HOME=usr/lib/jvm/java-8-openjdk-amd644.3 core-site.xmlCreate temporary directory$ sudo mkdir -p /app/hadoop/tmp$ sudo chown hduser:hadoop /app/hadoop/tmpOpen the file,$sudo nano /usr/local/hadoop/etc/hadoop/core-site.xmlAppend the following between configuration tags. Same as below.<configuration><property>?<name>hadoop.tmp.dir</name>?? <value>/app/hadoop/tmp</value>?? <description>A base for other temporary directories.</description></property><property>? <name>fs.default.name</name>?? <value>hdfs://localhost:54310</value>??? <description>The name of the default file system.? A URI whose scheme and authority determine the FileSystem implementation.? The uri’s scheme determines the config property (fs.SCHEME.impl) naming the FileSystem implementation class.? The uri’s authority is used to determine the host, port, etc. for a filesystem.</description>?</property></configuration>4.4 hdfs-site.xmlMainly there are two directories,Name NodeData NodeMake directories$ sudo mkdir -p /usr/local/hadoop_store/hdfs/namenode$ sudo mkdir -p /usr/local/hadoop_store/hdfs/datanode$ sudo chown -R hduser:hadoop /usr/local/hadoop_storeOpen the file,$sudo nano /usr/local/hadoop/etc/hadoop/hdfs-site.xmlChange the content between configuration tags shown as below.<configuration>?<property>? <name>dfs.replication</name>? <value>1</value>? <description>Default block replication.The actual number of replications can be specified when the file is created. The default is used if replication is not specified in create time.? </description>?</property>?<property>?? <name>dfs.namenode.name.dir</name>?? <value>file:/usr/local/hadoop_store/hdfs/namenode</value>?</property>?<property>?? <name>dfs.datanode.data.dir</name>?? <value>file:/usr/local/hadoop_store/hdfs/datanode</value>?</property></configuration>4.5 yarn-site.xmlOpen the file,$sudo nano /usr/local/hadoop/etc/hadoop/yarn-site.xmlJust like the other two, add the content to configuration tags.<configuration>?? <property>????? <name>yarn.nodemanager.aux-services</name>????? <value>mapreduce_shuffle</value>?? </property></configuration>STEP 5- Format Hadoop file systemHadoop installation is now done. All we have to do is change format the name-nodes before using it.$ hadoop namenode -formatSTEP 6- Start Hadoop daemonsNow that hadoop installation is complete and name-nodes are formatted, we can start hadoop by going to following directory.$ cd /usr/local/hadoop/sbin$ start-all.shJust check if all daemons are properly started using the following command:$ jpsSTEP 7 – Stop Hadoop daemonsStep 7 of hadoop installation is when you need to stop Hadoop and all its modules.$ stop-all.shAppreciate yourself because you’ve done it. You have completed all the Hadoop installation steps and Hadoop is now ready to run the first program.Let’s run MapReduce job on our entirely fresh Hadoop cluster setupGo to the following directory$ cd /usr/local/hadoopRun the following commandhadoop jar ./share/hadoop/mapreduce/hadoop-mapreduce-examples-3.0.0.jar pi 2 5Conclusion: Thus, we have successfully Install Hadoop on Ubuntu single node cluster.Viva Voce QuestionWhat is UBUNTU?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What are the Steps to install Ubuntu?____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject TeacherEXPERIMENT NO- 10Aim: Perform word count in hadoop in single node cluster using Map reduce.Theory: In this we will run a wordcount mapreduce example in hadoop using command line. This can be also an initial test for your Hadoop setup testing.1. PrerequisitesYou must have running hadoop setup on your system. If you don’t have hadoop installed visit?Hadoop installation on Linux?tutorial.2. Copy Files to Namenode FilesystemAfter successfully formatting namenode, You must have start all Hadoop services properly. Now create a directory in hadoop filesystem.$ hdfs dfs -mkdir -p /user/hadoop/inputCopy copy some text file to hadoop filesystem inside input directory. Here I am copying LICENSE.txt to it. You can copy more that one files.$ hdfs dfs -put LICENSE.txt /user/hadoop/input/3. Running Wordcount CommandNow run the wordcount mapreduce example using following command. Below command will read all files from input folder and process with mapreduce jar file. After successful completion of task results will be placed on output directory.$ cd $HADOOP_HOME$ hadoop jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.6.0.jar wordcount input output4. Show ResultsFirst check the names of result file created under?dfs@/user/hadoop/output filesystem using following command.$ hdfs dfs -ls /user/hadoop/outputNow show the content of result file where you will see the result of wordcount. You will see the count of each word.$ hdfs dfs -cat /user/hadoop/output/part-r-00000Conclusion: Thus, we have successfully Perform word count in hadoop in single node cluster using Map reduce.Viva Voce QuestionWhat is HADOOP?___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________What are the Steps to install word count in Map reduce?____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Signature of Subject Teacher ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download