Scribd
Upload
3 views19 pages

SQL notes

MySQL is an open-source RDBMS that uses SQL for managing data stored in tables. It supports various data models, including relational, hierarchical, network, and object-oriented, and provides commands for data definition, manipulation, and transaction control. Key concepts include relations, tuples, attributes, and integrity constraints, with commands for creating, accessing, and modifying databases and tables.

Uploaded by

girisanth45
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
3 views19 pages

SQL notes

MySQL is an open-source RDBMS that uses SQL for managing data stored in tables. It supports various data models, including relational, hierarchical, network, and object-oriented, and provides commands for data definition, manipulation, and transaction control. Key concepts include relations, tuples, attributes, and integrity constraints, with commands for creating, accessing, and modifying databases and tables.

Uploaded by

girisanth45
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
nat C It is freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language(SQL). In MySQL database , information is stored in Tables. A single MySQL database can contain many tables at once and store thousands of individual records. L sred Qui e) SQL is a language that enables you to create and operate on relational databases, which are sets of related information stored in tables. DIFFERENT DATA MODELS A data model refers ta a set of concepts to deseribe the structure of a database, and certain constraints (restrictions) that the database should obey. The four data model that are used for database management are 1. Relational data medel : In this data model, the data is organized into tables (ie. rows and columns). These tables are called relations. 2. Hierarchical data model 3. Network data model 4. Object Oriented data model TIONAL MODEL TERMINOLOGY 1. Relation : A table storing logically related data is called a Relation, 2. Tuple : A row of a relation is generally referred to as a tuple 3, Attribute : A column of a relation is generally referred to as an attribute. 4, Degree : This refers to the number of attributes in 2 relation, 5. Cardin lity : This refers to the number of tuples in a relation. 6, Primary Key : This refers to a set of one or more attributes that can uniquely identify tuples within the relation, 7. Candidate Key : All attribute combinations inside a relation that can serve as primary key are candidate keys as these are candidates for primary key position. 8. Alternate Key : A candidate key that is not primary key, is called an alternate key, 9. Foreign Key = A non-key attribute, whose values are derived from the primary key of some other table, Is known as foreign key in its current table, REFERENTIAL INTEGRITY = A referential integrity is a system of rules that @ DBMS uses to ensure that relationships between records in related tables are valid, and that users dan’t accidentally delete or change related data, This integrity is ensured by foreign key. CLASSIFICATION OF SOL STATEMENTS SQL commands can be mainly divided into following categories 1. Data Definition Language(DDL} Commands Commands that allow you to perform task, related to data definition e.g; * Creating, altering and dropping. © Granting and revoking privileges and roles. © Maintenance commands, 2. Data Manipulation Language{DML) Commands Commarids that allow you to perform data manipulation e.g., retrieval, insertion, deletion and modification of data stored in a database. 3, Transaction Control Language(TCL) Commands Commands that allow you to manage and control the transactions eg., ‘Making changes to database, permanent * Undoing changes to database, permanent © Creating savepoints © Setting properties for current transactions. MySQl ELEMENTS: 1. Literals 2. Datatypes 3. Nulls 4. Comments LITERALS Itrefer to a fixed data value. This fixed data value may be of character type or numeric type. For example, ‘replay’ , ‘Raj, 'B°, '306' are all character literals. Numbers not enclosed in quotation marks are numeric literals. E.g. 22, 18, 1997 are all numeric literals. Numericliterals can either be integer literals i<,, without any decimal or be real literals i.e. with a decimal point e.g. 17is an integer literal but 17.0 and 17.5 are real literals, DATA TYPES Data types are means to identify the type of data and associated operations for handling it. MySQL data types are divided into three categories: Numeric te andttime String types Numeric Data Type 1. int used for number without decimal, 2. Decimal(m.d) - used for floating/real numbers. m denotes the total length of number and ¢ is number af decimal digits. Date and Time Data Tye 1, date —used to store date in YYYY-MM-DD format. 2. time — used to store time in HH:MM&SS format. ‘String Data Types 1. char(m) — used to store a fixed length string. m denotes max. number of characters, 2. varchar{m) ~used to store-a wariable length string. m denotes max. no. of characters DIFFERENCE BETWEEN CHAR AND VARCHAR DATA TYPE [Link]. | Char Datatype Varchar Datatype 1 it specifies a fixed length character It specifies a variable length character string. String. 2 When a column is given datatype as __| When a column is given datatype as VARCHAR(n), CHAR(n), then MySQLensures thatall | then the maximum size a value in this column can values stored in that column have this | have is n bytes. Each value that is stored in this length Le. n bytes. if value ts shorter | column store exactly as you specify it i.e. no blanks than this length m then blanks are are added |f the length is shorter than maximum, added, but the size of value remains —_| lengthn. n bytes. (NULL VALUE If.a column in a row has no value, then column is said to be null , or to contain a null. You should use-a null value when the actual value is not known or when a value would not be meaningful. DATABASE COMMNADS [Link] EXISTING DATABASE To view existing database names, the command is : SHOW DATABASE: [Link] DATABASE IN MYSQL For creating the database in MySQL, we write the following command : CREATE DATABASE ; 2.8. In order to create a database Student, command is; CREATE DATABASE Student ; (B_ACCESSING DATABASE For accessing already existing database , we write USE ; e.g, to access 4 database named Student , we write command as : USE Student; DELETING DATABASE For deleting any existing database , the command is : DROP DATABASE ; e.g, to delete a database , say student, we write command as; DROP DATABASE Student ; 5. VIEWING TABLE IN DATABASE In order to view tables present in currently accessed database , command is : SHOW TABLES ; (CREATING TABLES IN MYSQL + Tables are created with the CREATE TABLE command. When a table is created, its columns are named, data types and sizes are supplied for each colurnn, Syntax of CREATE TABLE command is : CREATE TABLE («column name> , , ‘ order to create table EMPLOYEE given below : ECODE ENAME GENDER GRADE Ca We write the following command : ‘CREATE TABLE employee ( ECODE integer, ENAME varchar(20} , GENDER char(1), GRADE char{2), GROSS integer NSERTING DATA INTO TABLE ~The rows are added to relations(table) using INSERT command of SQL. Syntax of INSERT is : INSERT INTO [] VALUE ( , ,....); &.g. to enter a row into EMPLOYEE table (created above), we write command as: INSERT INTO employee VALUES(1001,, ‘Ravi’, » “EA” , 50000); OR INSERT INTO emplayee (ECODE , ENAME , GENDER , GRADE , GROSS) VALUES(1001, ‘Ravi? ‘Ea’ , 50000); ECODE ENAME GENDER GRADE GROSS 1001 Ravi M 4 50000 In order to insert another row in EMPLOYEE table , we write again INSERT command : INSERT INTO employee ‘VALUES(1002 , ‘Akash’ INSERTING NULL VALUES - Tolnsert value NULL in a specific column, we can type NULL without quotes and NULL will be inserted in that column. E.g. in onder to Insert NULL value in ENAME column of above table, we write INSERT command as : INSERT INTO EMPLOYEE VALUES (1004 , NULL, ‘M’ , B2’, 38965 ) ; ECODE ENAME ‘GENDER ‘GRADE GROSS 1001, Ravi M. ea 50000 1002 ‘Akash M. AL 35000 1004 NULL M. B2 38965 SIMPLE QUERY USING SELECT COMMAND = The SELECT command is used to pull information fram a table Syntax of SELECT command is : SELECT , FROM WHERE ; SELECTING ALL DATA - _ Inorder to retrieve everything (all columns) from a table, SELECT command is used as : SELECT * FROM ; a8 In order to retrieve everything from Employee table, we write SELECT command as EMPLOYEE SELECT * FROM Employee ; SELECTING PARTICULAR COLUMNS EMPLOYEE ECODE ENAME GENDER GRADE GROSS 1001 Ravi M 1002 Akash M 1004 Neela E 1005, ‘Sunny M 1006 Ruby F 1009 Neema F A particular column from a table can be selected by specifying column-names with SELECT command. E.g. in above table, if we want to select ECODE and ENAME column, then command is : ‘SELECT ECODE , ENAME FROM EMPLOYEE 5 £.g.2 in order to select only ENAME, GRADE and GROSS column, the command SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ; SELECTING PARTICULAR ROWS ‘We can select particular rows from a table by specifying a condition through WHERE clause along with SELECT statement, E.g. In employee table if we want ta select rows where Gender is female, then command is SELECT * FROM EMPLOYEE WHERE GENDER = ‘F"; E.g.2. in order to select rows where salary is greater than 48000, then command is : SELECT * FROM EMPLOYEE WHERE GROSS > 48000 ; ELIMINATING REDUNDANT DATA The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement, For example , SELECT GENDER FROM EMPLOYEE ; GENDER Mi ™ f M F F SELECT DISTINCT(GENDER) FROM EMPLOYEE ; [DISTINCTIGENDER) _| M F ‘VIEWING STRUCTURE OF A TABLE ~ If we want to know the structure of a table, we can use DESCRIBE or DESC command, as per following syntax : DESCRIBE | DESC ; ‘ta view the structure af table EMPLOYEE, command is; DESCRIBE EMPLOYEE ; OR DESC EMPLOYEE ; USING COLUMN ALIASES ‘The columns that we select in a query can be given a different name, |.¢. column alias name for output purpose. FROM ; eg: [n output, suppose we want te display ECODE column as EMPLOYEE_CODE in output , then command is : SELECT ECODE AS “EMPLOYEE_CODE” FROM EMPLOYEE ; SELECT AS column alias , AS column alias... C CONDITION BASED ON A RANGE ‘The BETWEEN operator defines a range of values that the column values must fall in to make the condition true. The range include both lower value and upper value. e.g. to display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is: ‘SELECT ECODE , FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ; Output will be : (ME ,GRADE CODE [ ENAME [GRADE 1001 | Ravi Ea 1006 [Ruby [Ar ‘CONDITION BASED ON A LIST To specify a list of values, IN operator is used. The IN operator selects value that match any value in @ given list of values. E.g SELECT * FROM EMPLOYEE WHERE GRADE IN (‘AJ , ‘A2'); ‘Output will be : ECODE | ENAME [GENDER] GRADE | GROSS 1002_| Akash | M Al 35000 3005__| Ruby | F AL 45000 1005 Sunny _|M AD 30000 1009 Neema_| F A2 52000, + The NOTIN operator finds rows that do not match in the list. E.g SELECT * FROM EMPLOYEE WHERE GRADE NOTIN (‘A1", ‘A2’); Output will be : ECODE | ENAME |GENDER| GRADE | GROSS oor [Ravi__[M ea 50000 1004 Neela F B2 38965. CONDITION BASED ON PATTERN MATCHES - UKE operator is used for pattern matching in SQL Patterns are described using two special wildcard characters: 1. percent{%) —The % character matches any substring, 2, underscore(_)- The _ character matches any character. 2.g. to display names of emplayee whose name starts with R in EMPLOYEE table, the command is : SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R54’ ; ‘Output will be : ENAME Raw Ruby @.g. to display details of employee whose second character in name is ‘e’. SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%" ; Output will be : ECODE | ENAME | GENDER| GRADE | GROSS 1004 | Neela [| F B2 38965 1009 [Neema [F Az 52000 @.g. to display details of employee whose name ends with “y'. SELECT * FROM EMPLOYEE WHERE ENAME LIKE "%y" ; Output will be : ECODE | ENAME | GENDER| GRADE | GROSS 1005_| Sunny _[M AD 30000 1006 [Ruby _[F AL 45000 iEARCHING FOR NULL The NULL value in a column can be searched for in a table using |S NULL in the WHERE clause. E.g. te list employee details whose salary contain NULL, we use the command : SELECT * FROM EMPLOYEE WHERE GROSS 1S NULL ; eB STUDENT NULL NULL to display the names of those students whose marks is NULL, we use the command : SELECT Name FROM EMPLOYEE WHERE Marks IS NULL ; Output will be: Name | ARUN SANJAY ‘SORTING RESULTS Whenever the SELECT query is executed , the resulting rows appear in a predecided order. The ORDER BY elause allow sorting of query result, The sorting can be done either in ascending or descending order, the default is ascending, ‘The ORDER BY clause is [Link] : SELECT , ... FROM ctablename> WHERE ORDER BY 40000 ORDER BY ENAME dese ; ‘Output will be: ENAME Ravi Ruby Neema MODIFYING DATA IN TABLES you can modify data in tables using UPDATE command of SQL. The UPDATE command specifies the rows to be changed using the WHERE clause, and the new data using the SET keyword. Syntax of update cammand is : UPDATE SET =value , =value WHERE ; e.g. to change the salary of employee of those in EMPLOYEE table having employee code 1009 to 55000. UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009; PDATING Mi LUMINS e.g. to update the salary to 58000 and grade to B2 for those employee whose employee code is 1001. UPDATE EMPLOYEE SET GROSS = 58000, GRADE='B2’ WHERE ECODE = 1009; C OTHER EXAMPLES ‘@.g.1. Increase the salary of each employee by 1000 in the EMPLOYEE ta UPDATE EMPLOYEE SET GROSS = GROSS +100 ; e.g.2. Double the salary of employees having grade as ‘Al’ or ‘AZ’ UPDATE EMPLOYEE SET GROSS = GROSS * 2; WHERE GRADE=’A1’ OR GRADE='A2’ ; e.g-3. Change the grade to ‘A2' for those employees whose emplayee code is 1004 and name is Neela. UPDATE EMPLOYEE SET GRADE='A2’ WHERE ECODE=1004 AND GRADE="NEELA’ ; DELETING DATA FROM TABLES To delete some data from tables, DELETE command is used, The DELETE command removes rows from a table. The syntax of DELETE command is DELETE FROM WHERE ; For example, to remove the details of those employee from EMPLOYEE table whose grade is A, DELETE FROM EMPLOYEE WHERE GRADE ="A1" ; ‘TO DELETE ALL THE CONTENTS FROM A TABLE DELETE FROM EMPLOYEE ; So if we do not specify any condition with WHERE clause, then all the rows of the table will be deleted, Thus above line will delete all rows from emplayee table. DROPPING TABLES The DROP TABLE command lets you drop a table from the database. The syntax of DROP TABLE command is: DROP TABLE ; e.g. to drop a table employee, we need to write DROP TABLE emplayee ; Once this command is given, the table name is no longer recognized and no more commands can be given on that table. After this command is executed, all the data in the table along with table structure will be deleted. [Link]. DELETE COMMAND DROP TABLE COMMAND 1 _[itisa ML command. itis a DOL Command, 2 | This commands used to delete only rows | This command is used to delete all the data of the table of data froma table alang with the structure of the table, The table is no longer recognized when this command gets executed. 3 _ | Syntox of DELETE command is: Syntax of DROP command is: DELETE FROM DROP TABLE ; WHERE ; ALTER TABLE COMMAND The ALTER TABLE command is used to change definitions of existing tables.(adding columns,deleting columns etc.) The ALTER TABLE command is used for ; 1. adding columns to a table 2. Modifying column-definitions of a table, 3. Deleting columns of a table, 4. Adding constraints to table. 5. Enabling/Disabling constraints, ADDING COLUMNS TO TABLE To add a column to a table, ALTER TABLE command can be used as per following syntax: ALTER TABLE ADD ; e.g. to add a new column ADDRESS to the EMPLOYEE table, we can write command as = ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(SO}; Anew column by the name ADDRESS will be added to the table, where each row will contain NULL value for the new column. ECODE ENAME, GENDER GRADE GROSS ‘ADDRESS 1001. Ravi M Ea 50000 NULL 1002 Akash M. AL 35000 NULL 1004. Neela_ £ B2 38965 NULL 1005, Sunny M AQ 30000 NULL 1006 Ruby F AL 45000 NULL 1009) Neema E AQ 52000 NULL However if you specify NOT NULL constraint while adding a new column, MySQL adds the new column with the default value of that datatype e.g. for INT type it will add 0 , For CHAR types, it will add a space, and so on. e.g. Given a table namely Testt with the following data in it. Colt, Col2 i 2 G Now following commands are-given for the table. Predict the table contents after each of the following statements: (i) ALTER TABLE testt ADD col3 INT; (ll) ALTER TABLE testt ADD cola INT NOT NULL ; i) ALTER TABLE testt ADD colS CHAR(3) NOT NULL ; (iv) ALTER TABLE testt ADD col6 VARCHAR(3); (MODIFYING COLUMNS Column name and data type of column can be changed as per following syntax : ALTER TABLE CHANGE cnew column name> ; If Only data type of column need to be changed, then ALTER TABLE MODIFY ; 24, In table EMPLOYEE, change the column GROSS to SALARY. ALTER TABLE EMPLOYEE CHANGE GROSS SALARY INTEGER; (£482, In table EMPLOYEE , change the column ENAME to EM_NAME and data tyne from VARCHAR{20) to VARCHAR(30). ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30); .£.3, In table EMPLOYEE , change the datatype of GRADE calumn from CHAR(2) to VARCHAR(2). ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2); ‘DELETING COLUMNS To delete a column from a table, the ALTER TABLE command takes the following form ALTER TABLE DROP ; e.g. 10 delete column GRADE from table EMPLOYEE, we will write ALTER TABLE EMPLOYEE DROP GRADE ; ADDING/REMOVING CONSTRAINTS TO A TABLE ALTER TABLE statement can be used to add constraints to your existing table by using it in following manner: RI a ‘ALTER TABLE ADD PRIMARY KEY (Column name); e.g. to add PRIMARY KEY constraint on column ECODE of table EMPLOYEE , the command is ALTER TABLE EMPLOYEE ADD PRIMARY KEY (ECODE) ; ‘TO ADD FOREIGN KEY CONSTRAINT. ALTER TABLE
ADD FOREIGN KEY (Column name) REFERENCES Parent Table (Primary key of Parent Table); REMOVING CONSTRAINTS. - To remove primary key constraint from a table, we use ALTER TABLE command as: ALTER TABLE DROP PRIMARY KEY ; = To remove foreign key constraint from a table, we use ALTER TABLE command as : ALTER TABLE
DROP FOREIGN KEY ; ENABLING/DISABLING CONSTRAINTS ‘Only foreign key can be disabled/enabled in MySQL. To disable foreign keys: SET FOREIGN_KEY_CHECKS = 0 To enable foreign keys: SET FOREIGN_KEY_CHECKS INTEGRITY CONSTRAINTS/CONSTRAINTS A constraint is.a condition er check appli Common types of constraints include : [Link]] Constraints Description ¢ NOT NULL Ensures that a column cannot have NULL value 2_| DEFAULT Provides a default value for a-calumn when none is specified 3 UNIQUE Ensures that all values in a column are different a__| CHECK Makes sure that all valves in a column satisfy certain criteria 5___| PRIMARY KEY Used to uniquely identify a row in the table 6 I FOREIGN KEY Used to ensure referential integrity of the data NOT NULL CONSTRAINT By default, a column can hold NULL. It you not want to allow NULL value in a column, then NOT NULL constraint must be applied on that column. E.g, CREATE TABLE Custorner {SID integer NOT NULL, Last_Mame varchar(30) NOT NULL , First_Name varchar(30)—) ; ‘Columns SID and Last_Name cannot include NULL, while First_Name can include NULL. An attempt to execute the following SQL statement, INSERT INTO Customer ‘VALUES (NULL, ‘Kumar’, ‘Ajay’); will result [Link] error because this will lead to column SID being NULL, which violates the NOT NULL constraint on that column. DEEAULT CONSTARINT The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. E.g. CREATE TABLE Student ( Student_ID integer, Name varchar(30) , Score integer DEFAULT 80); When following SQL statement is executed on table created above: INSERT INTO: Student __— _no-value has been provided for score field. WALUES (10 , ‘Ravi’ J Then table Student looks like the following: Student_iO_ | Name Score, 10 Ravi 30 + score field has got the default value UNIQUE CONSTRAINT The UNIQUE constraint ensures that all values in a column are distinct. In other words, no two rows can hold the same value for a column with UNIQUE constraint. eg. CREATE TABLE Custamer (iD integer Unique , Last_Name varchar(30} , First_Name varchar(30) ) ; Column SID has a unique constraint, and hence cannot include duplicate values. So, ifthe table already contains the following rows : SID__| Last_Name 1 Kumar Ravi 2 Sharia Alay 3 Devi Raj The executing the following SQL statement, INSERT INTO Customer VALUES ('3' ‘Cyrus’, ‘Grace’) ; will result in an error because the value 3 already exist in the SID column, thus trying to insert another row with that value violates the UNIQUE constraint. CHECK CONSTRAINT + The CHECK constraint ensures that all values in.a column satisfy certain conditions, Once defined, the table will only insert a new row or update an existing row if the new value satisfies the CHECK constraint, ee CREATE TABLE Customer {SID integer CHECK (S10 > o}, Last_Name varchar(30}, First_Name varchar(30) } 3 So, attempting to execute the following statement INSERT INTO Customer VALUES [-2 , ‘Kapoor’ , Raj’); will resultin an error because the values for SID must be greater than O. PRIMARY KEY CONSTRAINT - Aprimary key is used to identify each row in a table. A primary key can consist of one or more fields(column) ona table. When multiple fields are used as a primary key, they are called a composite key. = You can define a primary key in CREATE TABLE command through keywords PRIMARY KEY. e.g, CREATE TABLE Customer ( SID integer NOT NULL PRIMARY KEY, Last_Name varchar(30}, First_Name varchar(30) ) ; CREATE TABLE Customer (iD integer, Last_Name varchar(30} , First_Name varchar(30), PRIMARY KEY (SID) ); The latter way is useful if you want to specify a composite primary key, e.g. CREATE TABLE Customer (Branch integer NOT NULL, SID integer NOT NULL, Last_Name varchar(30},, First_Name varchar(30), PRIMARY KEY (Branch , SiD) ) ; ll INT Foreign key is a non key column of a table (child table) that draws its values fram primary key of another table(parent table). The table in which a foreign key is defined is called a referencing table or child table. & table to which a ‘foreign key points is called referenced table or parent table. eg. TABLE: STUDENT< Parent Table WOU [RM [OS i a Primary key. z DEF xt z KE £7] Child Table TABLE: SCORE ROU_NG | MARKS 1 55 [2 (|s 3 90 Here column Rall_No is a foreign key in table SCORE(Child Table) and it is drawing its values from Primary key (ROLL_NO) of STUDENT table.{Parent Key) ‘CREATE TABLE STUDENT { ROLL_NO integer NOT NULL PRIMARY KEY , NAME WARCHAR(30) , CLASS VARCHAR(3) ); CREATE TABLE SCORE ( ROLL_NG integer MARKS integer , FOREIGN KE¥(ROLL_NO) REFERNCES STUDENT(ROLL_NG}) ; “Foreign key Is always defined in the child table. ‘Syntax for using foreign key FOREIGN KEY(column name) REFERENCES Parent_Table(PK of Parent Table); REFERENCING ACTIONS Referencing action wi (ON DELETE clause determines what to do in case of a DELETE occurs in the parent table. Referencing action with ON UPDATE clause determines what to do in case of a UPDATE occurs in the parent table. Actions: 1. CASCADE : This action states that if a DELETE or UPDATE operation affects a row from the parent table, then automatically delete or update the matching rows in the child table i.e., cascade the action to child table. 2. SET NULL : [Link] states that if 2 DELETE ar UPDATE operation affects a row from the parent table, then set the foreign key column in the child table to NULL. 3, NO ACTION: Any attempt for DELETE or UPDATE in parent table is not allowed. 4. RESTRICT : This action rejects the DELETE or UPDATE operation for the parent table, Q: Create two tables Customer(customer_id, name) Customer_sales(transaction id, amount, customer_id) Underlined columns indicate primary keys and bold column names indicate foreign key. Make sure that no action should take place in case of a DELETE or UPDATE in the parent table. Sol : CREATE TABLE Customer ( customer_id int Not Null Primary Key , name varchar{30) } ; CREATE TABLE Customer_sales ( transaction_id Not Null Primary Key, amount int , ‘customer_id int, FOREIGN KEV{customer_id) REFERENCES Customer (customer_id) ON DELETE NO ACTION (ON UPDATE NO ACTION ); Q; Distinguish between a Primary Key and a Unique key in a table. ‘[Link].| PRIMARY KEY UNIQUE KEY 1. [Column having Primary key can’t contain _| Colum Raving Unique Key can contain NULL value NULL value 2_| There canbe only one primary key in Table, | Many columns can be defined as Unique key’ Q: Distinguish between ALTER Command and UPDATE command of SQL. [Link]. ALTER COMMAND UPDATE COMMAND 1, | itisa ODL Command tis a DML command 2 itis used to change the definition of itis used ta modify the data values present g table, Le. adding column, in the rows of the table deleting column, etc. 3, Syntax for adding column in a table: Syntax for using UPDATE command: ALTER TABLE UPDATE ADD ; SET =value WHERE : AGGREGATE / GROUP FUNCTIONS Aggregate / Group functions work upon groups of rows , rather than on single row, and return one single output, Different aggregate functions are : COUNT(), AVG(}, MIN(), MAX(), SUM () Table : EMPL EMPNO | ENAME JOB 8369) ‘SMITH CLERK. 'ANYA | SALESMAN. 8566 ‘AMIR | SALESMAN MANAGER SAL DEPTNO 2985, 10 20 8760 30. 8912, 3000) 10 1. AVGI) This function computes the average of given data. e.g. SELECT AVGISAL) FROM EMPL; Output AVG(SAL} 6051.6 2. COUNTI) This functian counts the number of rows ina given column, If you specify the COLUMN name in parenthesis of function, then this function returns rows where COLUMN is not null If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. e.g. SELECT COUNT(*) FROM EMPL; Output [couNT) | 5 €.g.2 SELECT COUNT(JO8) FROM EMPL; ‘Output COUNTUOR) a 3. MAX() ‘This function returns the maximum value from a given column or expression e.g. SELECT MAX(SAL) FROM EMPL; Output MANGAL) 9870 4. MIN() This function returns the minimum value from a given column or expression. e.g. SELECT MIN(SAL) FROM EMPL ; ‘Output [Minisayy | 2985 5. SUMI This function returns the sum ef values in given column or expression. e.g, SELECT SUMISAL) FROM EMPL ; ‘Output SUMISAL) 30258 GROUPING RESULT - GROUP BY The GROUP BY clause combines all those records{row) that have identical values in a particular field(column) or a group of fields(columns), GROUPING can be done by a column name, or with aggregate functians in which case the aggregate produces a value for each group. Table : EMPL EMPNO | ENAME J08 SAL DEPTNO 8365 SMITH CLERK 2985 10 8499) ANYA [SALESMAN | 9870 20 ‘8566, AMIR | SALESMAN | 8760 30 ‘8658 BINA | MANAGER | 5643 20 e.g. Calculate the number of employees in each grade. SELECT JOB, COUNT(*) FROM EMPL GROUP BY JOB; Output 08 COUNTI) CLERK 1 SALESMAN 2 MANAGER. i e.g.2. Calculate the sum of salary for each department. SELECT DEPTNO , SUM(SAL} FROM EMPL GROUP BY DEPTNO; ‘Output DEPTNO SUM(SA 10 2985 20 15513 30 8760 6.8.3. find the average salary of each department. * C ** One thing that you should keep in mind is that while grouping , you shoufd inciude only those values in the SELECT list that either have the some value for ¢ group or contain a grouploggregate} function. Like in e.g. 2 given above, DEPTNO column has one(same) volue for @ group and the other expression SUM/SAL) contains © group function, NESTED GROUP = Tocreate a group within a group -e., nested group, you need to specify multiple fields in the GROUP BY expression. e.g. To group records jab wise within Deptno wise, you need to issue a query statement like : SELECT DEPTNO , JOB , COUNT(EMPNO) FROM EMPL GROUP BY DEPTNO , JOB; Output DEPTNO _[j08 COUNTIEMPNO) 10 CLERK 1 20 SALESMAN. 1 20 MANAGER 1 30. SALESMAN. 1 PI INDITION ON GR HAV! E ~The HAVING clause places conditions on groups in contrast to WHERE clause that places condition on individual rows. \while WHERE conditions cannot include aggregate functions, HAVING conditions can do so. - _€g. To display the jabs where the number of employees is less than 2, SELECT 108, COUNT(*) FROM EMPL GROUP BY JOB HAVING COUNT(*) < Output 08 ‘COUNT(*) CLERK 1 MANAGER. 1 DATABASE TRANSACTIONS TRANSACTION A Transaction is a logical unit of work that must succeed or fail In its entirety. This statement means that a transaction may involve many sub steps, which should either all be carried out successfully or all be ignored if some failure occurs. A Transaction is an atomic operation which may not be divided into smaller operations. Example of a Transaction Begin transaction Get balance from account X Calculate new balance as X - 1000 Store new balance into database file Get balance fram aceaunt ¥ Calculate new balance as ¥ + 1000 Store new balance into database file End transaction TRANSACTION PROPERTIES (ACID PROPERTIES) ‘LATOMICITY(All of None Concept} — This property ensures that either all operations of the transaction are carried out or none are. 2. CONSISTENCY — This property implies that if the database was in a consistent state before the start of transaction execution, then upon termination of transaction, the database will also be in a consistent state. 2. ISOLATION — This property implies that each transaction is unaware of other transactions executing concurrently in the system. 4, DURABILITY This property of a transaction ensures that after the successful completion of a transaction, the changes made by it to the database persist, even if there are system failures. TRANSACTION CONTROL COMMANDS (TCL) The TCL of MySQL consists of following commands : 1. BEGIN of START TRANSACTION — marks the beginning of a transaction. 2. COMMIT — Ends the current transaction by saving database changes and starts a new transaction. 3, ROLUBACK — Ends the current transaction by discarding database changes and starts anew transaction. 4, SAVEPOINT — Define breakpoints for the transaction to allow partial rollbacks. 5. SET AUTOCOMMIT — Enables or disables the default auto commit mode

You might also like