session_2 on database mysql databaseds from file to

databaseds from file to new concepts wdwsw

1. MySQL Data Types

2. Introduction Numeric DATETIME DATE TIMESTAMP String ► MySQL supports a number of SQL standard data types in various categories. Following Data types are are based on MySQL community server 5.6

3. © w3resource Numeric Types

4. Integer Types Type Length in Bytes Minimum Value (Signed) Maximum Value (Signed) Minimum Value (Unsigned) Maximum Value (Unsigned) TINYINT 1 -128 127 255 SMALLINT 2 -32768 32767 65535 MEDIUMINT 3 -8388608 8388607 to 16777215 INT 4 -2147483648 2147483647 4294967295 BIGINT 8 -9223372036854775808 92233720368 54775807 184467440737 09551615

5. Floating-Point Types Type Length in Bytes Minimum Value (Signed) Maximum Value (Signed) Minimum Value (Unsigned) Maximum Value (Unsigned) FLOAT 4 -3.402823466E+38 -1.175494351E-38 1.175494351E-38 3.402823466E+38 DOUBLE 8 -1.7976931348623 157E+ 308 -2.22507385850720 14E- 308 0, and 2.22507385850720 14E- 308 1.797693134862315 7E+ 308

6. Fixed-Point Types ► In standard SQL the syntax DECIMAL(5,2) (where 5 is the precision and 2 is the scale.) be able to store any value with five digits and two decimals. Therefore the value range will be from -999.99 to 999.99. The syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0). MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10. If the scale is 0, DECIMAL values contain no decimal point or fractional part. The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column.

7. Bit Value Types ► The BIT data type is used to store bit-field values. A type of BIT(N) enables storage of N-bit values. N can range from 1 to 64. ► To specify bit values, b'value' notation can be used. ‘ value ’ is a binary value written using zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively

8. Date and Time Types

9. DATETIME, DATE, and TIMESTAMP Types Description Display Format Range DATETIME Use when you need values containing both date and time information. YYYY-MM-DD HH:MM:SS '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. DATE Use when you need only date information. YYYY-MM-DD '1000-01-01' to '9999-12-31'. TIMESTAMP Values are converted from the current timezone to UTC while storing, and converted back from UTC to the current time zone when retrieved. YYYY-MM-DD HH:MM:SS '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

10. Time Type ► MySQL fetches and displays TIME values in 'HH:MM:SS' format or 'HHH:MM:SS' format The range of. TIME values from '-838:59:59' to '838:59:59'. The hours part may be rather large because not only the TIME type can be used to represent the time of day, i.e. less than 24 hours, but also the passed time or a time of interval between two events. ► The TIME values in MySQL can be recognized in different formats, some of which can include a trailing fractional seconds part in up to 6 digits microseconds precision. The range for TIME values is '-838:59:59.000000' to '838:59:59.000000'.

11. Year Type ► The YEAR type is a 1-byte type used to represent year values. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. If no width is given the default is four characters String length Range 4-digit string '1901' to '2155'. 4-digit number 1901 to 2155. 1- or 2-digit string '0' to '99'. Values in the ranges '0' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999. 1- or 2-digit number 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.

12. String Types

13. CHAR and VARCHAR Types ► The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained. Types Description Display Format Range in characters CHAR Contains non-binary strings. Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length. Trailing spaces are removed. The length can be any value from 0 to 255. VARCHAR Contains non-binary strings. Columns are variable-length strings. As stored. A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

14. BINARY and VARBINARY Types ► The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. Types Description Range in bytes BINARY Contains binary strings. 0 to 255 VARBINARY Contains binary strings. A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

15. BLOB and TEXT Types Types Description Categories Range BLOB Large binary object that containing a variable amount of data. Values are treated as binary strings.You don't need to specify length while creating a column. TINYBLOB Maximum length of 255 characters. MEDIUMBLOB Maximum length of 16777215 characters. LONGBLOB Maximum length of 4294967295 characters TEXT Values are treated as character strings having a character set. TINYBLOB Maximum length of 255 characters. MEDIUMBLOB Maximum length of 16777215 characters. LONGBLOB Maximum length of 4294967295 characters

16. Miscellaneous Type A string object whose value is chosen from a list of values given at the time of table creation. For example - CREATE TABLE length ( length ENUM('small', 'medium', 'large') ); ► ENUM Types ► Set Types A string object having zero or more comma separated values (maximum 64). Values are chosen from a list of values given at the time of table creation.

17. NULL Missing/unknown/inapplicable data represented as a NULL value NULL is not a data value. It is just an indicator that the value is unknown

18. SQL Data Definition Language

19. Data Definition Language Create Alter Drop Truncate

20. CREATE TABLE Syntax CREATE TABLE TABLE_NAME(COLUMN_NAME DATATYPE[……]); Example CREATE TABLE EMPLOYEE(NAME VARCHAR(20),EMAIL VARCHAR(50),DOB DATE);

21. CONSTRAINTS NOT NULL DEFAULT UNIQUE PRIMARY FOREIGN CHECK INDEX

22. CREATE TABLE EXAMPLE Implementing PRIMARY KEY ,NOT NULL and UNIQUE Customer Table Colum name Datatype Description Constraints CustomerId Varchar(6) Unique id generated for each customer Primary Key CustomerName Varchar(30) Name of the customer Not null DateOfReg Date Date on which the customer registered   UserId Varchar(15) Decided at the time of registration It should be unique Password Varchar(15) Decided at the time of registration Not Null

23. CREATE TABLE EXAMPLE Implementing PRIMARY KEY ,NOT NULL and UNIQUE Example: CREATE TABLE CUSTOMER(ID VARCHAR(6) PRIMARY KEY, CUSTOMERNAME VARCHAR(30) NOT NULL, DATEOFREG DATE,USERID VARCHAR(15) UNIQUE,PASSWORD VARCHAR(15) NOT NULL);

24. Create Table – Contd., Implementation of Composite Primary Key and Foreign Key Constraints BankInfo Table Colum name Datatype Description Constraints AccountNo Number(10) Account no of customer Composite Primary key CustomerId Varchar(6) Unique id provided to each customer when he/she is registered to purchase items Foreign key referring to customer table

25. Create Table – Contd., Implementation of Composite Primary Key and Foreign Key Constraints EXAMPLE : Create table Bankinfo ( AccountNo int , CustomerId varchar(6) , bankinfo int REFERENCES customer(id) );

26. Create Table – Contd., Implementation of Self Referencing Foreign key in Employee_Details Table Column Name Data type Constraints Employee_ID Number(6) Primary key of the table Employee_Last_Name Varchar2(20) Employee_Mid_Name Varchar2(3) Employee_First_Name Varchar2(20) Employee_Email Varchar2(30) Employee_Dept Number(2) Default ‘HR’ Manager_ID Varchar2(30) It can take only those values which are present in Employee_ID column

27. Create Table – Contd., Implementing Self Referential Foreign Key EXAMPLE : CREATE TABLE Employee_Details ( Employee_ID Number(5) CONSTRAINT Employee_PKey PRIMARY KEY , Employee_Last_Name Varchar2(20), Employee_Mid_Name Char(3), Employee_First_Name Varchar2(20), Employee_Email Varchar2(30), Department Varchar2(10) default ‘HR’, Manager_ID Number(5) CONSTRAINT Manager_FKey REFERENCES Employee_Details ( Employee_ID ));

28. Create Table – Contd., Implementing Check Item Table Colum name Datatype Description Constraints ItemId Varchar2(6) Unique Id provided for each item. (e.g. STN001 for stationery items) Primary Key ItemName Varchar2(30) Name of the item Not Null QtyOnHand Number(3) Current availability of item in the shop  Should be greater than Re- OrderLevel (Table level constraint) UnitPrice Number(6,2) Sell price of item per unit Greater than 0 Class Char(1) Depending on the Unit Price, items belongs to various Classes. E.g : A,B,C etc. Class of Item is ‘A’ if UnitPrice is less than 100, ‘B’ if UnitPrice is less than 1000, ‘C’ if UnitPrice is 1000 and above (Table level constraint) UnitOfMeasurement Varchar2(12) Unit used to measure the quantity (e.g. Kilogram, dozen, etc.)   ReOrderLevel Number(3) Minimum Quantity after which the supplier must be ordered for new stock  Greater than 0 ReorderQty Number(3) Minimum Quantity that can be ordered to the supplier  Greater than 0 Discount Number(2) Percentage discount on the item to the customer  

29. Create Table – Contd., Implementing Check constraint EXAMPLE : CREATE TABLE students ( student_ID int NOT NULL, student_Name varchar(255) NOT NULL, class_name varchar(255) NOT NULL, Age int CHECK(Age >9) );

30. Create Table – Contd., Implementing Composite Foreign key constraint Billing Table Colum name Datatype Description Constraints BillId Number(4) Unique Id generated for each bill Primary key AccountNo Number(10) Account no which is used to pay the bill Composite Foreign key to Bank info table CustomerId Varchar2(6) Customer id of the customer who does the purchase of items BillDate Date The date of payment of bill  Default SYSDATE PaymentType Varchar2(12) Type of Payment Either Credit card or Debit card

31. Create Table – Contd., Implementing Composite Foreign key constraint EXAMPLE : Create table Billing ( BillId number(4) constraint billing_billid_pk PRIMARY KEY,Accountno number(10) , CustomerId varchar2(6) , BillAmt number(7,2) constraint billing_billamt_Nnull NOT NULL,Billdate date DEFAULT sysdate,PaymentType varchar2(12) constraint billing_paymenttype CHECK( PaymentType in (‘ Creditcard ', ‘ Debitcard ') ),constraint billing_composite_fk FOREIGN KEY( AccountNo , CustomerId ) references BankInfo ( AccountNo , CustomerId ));

32. ALTER TABLE– ADD/DROP/MODIFY Column Syntax: ALTER TABLE tablename (ADD/MODIFY/DROP column_name ) Example ALTER TABLE Customer ADD Contact_Phone Char(10);

33. Example – Contd., ALTER TABLE Customer MODIFY Contact_Phone Char(12); ALTER TABLE Customer DROP Contact_Phone ;

34. ALTER TABLE– ADD/DROP/ MODIFY Column – Contd., Used to modify the structure of a table by adding and removing columns. The ALTER TABLE statement with MODIFY option cannot be used to change the name of a column or table.

35. ALTER TABLE—ADD/DROP Constraint ALTER TABLE Customer_Account_Details ADD CONSTRAINT Pkey1 PRIMARY KEY ( Account_No ); ALTER TABLE Customer_Account_Details ADD CONSTRAINT Pkey2 PRIMARY KEY ( Account_No , Cust_ID );

36. ALTER TABLE—ADD/DROP Constraint – Contd., ALTER TABLE Customer_Account_Details DROP PRIMARY KEY; Or ALTER TABLE Customer_Account_Details DROP CONSTRAINT Pkey1;

37. ALTER TABLE—ADD/DROP Constraint – Contd., ALTER TABLE Customer_Transaction ADD CONSTRAINT Fkey1 FOREIGN KEY ( Cust_ID ) REFERENCES Customer_Account_Details ( Cust_ID ); ALTER TABLE Customer_Transaction DROP CONSTRAINT Fkey1;

38. ALTER TABLE—ADD/DROP Constraint – Contd., A table can have one or more Foreign keys Adding a foreign key constraint using ALTER TABLE command will result in an error if the existing data in master or child table does not support the foreign key restriction.

39. ALTER TABLE—ADD/DROP Constraint – Contd., ALTER TABLE statement can be used to Add or Drop primary key constraint to / from a table ALTER TABLE statement can be used to Add or Drop foreign key constraint to / from a table ALTER TABLE statement can be used to Add or Drop Unique constraint to/ from a table ALTER TABLE statement can be used to Add or Drop check constraint to / from a table

40. Drop default and check

41. ALTER TABLE—ADD/DROP Constraint – Contd., If a table already has a primary key, then adding a primary key using the ALTER TABLE statement results in an error. RDBMS will not allow a PRIMARY KEY constraint (using the ALTER TABLE statement) on column(s) if the column(s) has NULL or duplicate values

42. CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, firstname varchar(50) NOT NULL, lastname varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE contact ( id INT, customer_id INT, info varchar(50) NOT NULL, type varchar(50) NOT NULL ) ENGINE=INNODB; ALTER TABLE contact ADD INDEX par_ind ( customer_id ); ALTER TABLE contact ADD CONSTRAINT fk_customer FOREIGN KEY ( customer_id ) REFERENCES customer ( id ) ON DELETE CASCADE ON UPDATE RESTRICT;

43. SQL - DROP TABLE DROP TABLE Deletes table structure Cannot be recovered Use with caution DROP TABLE UnqTable ;

44. Truncate Table Deleting All Rows of a table TRUNCATE TABLE Customer;

45. SQL Data Manipulation Language

46. SQL - INSERT INTO Insert into customer values(‘C4',‘Allan','13-Mar-09',’Allan1004',’Allan@123'); C4 Allan 13-Mar-09 Allan1004 Allan@123 CustomerId CustomerNAme DateOfRegistration UserId Password C1 John 1-Mar-09 John1001 John@123 C2 Jack 10-Mar-09 Jack1002 Jack@123 C3 Bob 12-Mar-09 Bob1003 Bob@123 C4 Allan 13-Mar-09 Allan1004 Allan@123

47. SQL - INSERT INTO– Contd., Inserting NULL in to the table-Method1 Insert into customer values('C5','Simon',NULL,'Symon1005','Symon@123'); C5 Simon NULL Symon1005 Symon@123

48. SQL - INSERT INTO– Contd., Inserting NULL in to the table-Method2 Insert into customer( CustomerId , CustomerName , UseId , Password) values( 'C5', 'Simon', 'Symon1005', 'Symon@123'); C5 Simon NULL Symon1005 Symon@123

49. SQL - DELETE FROM With or without WHERE clause DELETE FROM tablename WHERE condition Deleting All Rows DELETE FROM Customer ; Deleting Specific Rows DELETE FROM Customer WHERE CustomerId = ‘C1’;

50. Difference Between Delete and Truncate DELETE TRUNCATE Data can be recovered Data cannot be recovered DML statement DDL statement DELETE does not release the memory occupied by the records of the table TRUNCATE releases the memory occupied by the records of the table

51. SQL - UPDATE Syntax: UPDATE tablename SET column_name =value [ WHERE condition] Updating All Rows Updating Particular rows UPDATE Customer SET DateOfReg = NULL; UPDATE Customer SET DateOfReg = NULL Where CustomerId = 'C1';

52. SQL – UPDATE – Contd., Updating Multiple Columns UPDATE Customer SET DateOfReg = NULL , Password = ‘John@321’ WHERE CustomerId = ‘C1’;

53. Retrieving All columns from a table To select set of column names, Syntax: SELECT column1, column2,… FROM TableName Example: SELECT * FROM Customer;

54. Retrieving Few Columns Retrieving only CustomerID and UserId from Customer Table Implementing Customized Columns Names SELECT CustomerId , UserId FROM Customer; SELECT CustomerId AS “Customer Identification”, UserId AS “User Identification” FROM Customer;

55. SQL - ALL, DISTINCT Get all Customers Name OR Get all distinct Customer Name SELECT ALL CustomerName FROM Customer; SELECT CustomerName FROM Customer; SELECT Distinct CustomerName FROM Customer;

56. Retrieving Rows based on Condition Syntax SELECT COL1,COL2,….. FROM TABLE NAME; WHERE <SEARCH CONDITION>

57. Retrieving a subset of rows (Working of WHERE Clause) Problem Statement: To select CustomerId and UserId of the customer whose date of registration is 13 March 2009. CustomerId CustomerName DateOfRegistration UserId Password C1 John 1-Mar-09 John1001 John@123 C2 Jack 10-Mar-09 Jack1002 Jack@123 C3 Bob 12-Mar-09 Bob1003 Bob@123 C4 Allan 13-Mar-09 Allan1004 Allan@123 C5 Simon   Symon1005 Symon@123

58. Retrieving a subset of rows (Working of WHERE Clause) – Contd., SELECT CustomerId , UserId FROM Customer WHERE DateOfReg ='13-Mar-2009'; CustomerId UserId C4 Allan1004

59. Relational operators List all items whose unit price is > 100 List the CustomerId and UserId of ‘Allan’ Relational operators = , < , > , <= , >= , != or < > SELECT ItemId , ItemName FROM ITEM WHERE UnitPrice > 100; SELECT CustomerId , USerId FROM Customer WHERE CustomerName =‘Allan’;

60. Relational operators – Contd., List all items where discount is at least 10 percent. SELECT ItemId , ItemName FROM ITEM WHERE Discount > 10;

61. Logical operators List all items where Unit Price is less than 100 and Unit of measurement is ‘Dozen’. SELECT ItemId , ItemName FROM ITEM WHERE UnitPrice < 100 AND UnitOfMeasurement = ‘Dozen’;

62. Logical operators – Contd., List all items where either the unit price is less than 100 or Unit of measurement is ‘Dozen’ List all items whose Unit Price is not less than 100 . SELECT ItemId , ItemName FROM ITEM WHERE UnitPrice < 100 OR UnitOfMeasurement = ‘Dozen’; SELECT ItemId , ItemName FROM ITEM WHERE NOT UnitPrice < 100;

63. Retrieval using BETWEEN List all Item with Unit Price in the range 100 to 200. OR SELECT ItemId , ItemName FROM ITEM WHERE UnitPrice >= 100 AND UnitPrice <= 200; SELECT ItemId , ItemName FROM ITEM WHERE UnitPrice BETWEEN 100 AND 200;

64. Retrieval using IN List all items which have Unit of measurement as ‘Kilogram’ or ’Dozen’. OR SELECT ItemId , ItemName FROM ITEM WHERE UnitofMeasurement =‘Kilogram’ OR UnitOfMeasurement = ‘Dozen’; SELECT ItemId , ItemName FROM ITEM WHERE UnitOfMeasurement IN(‘ Kilogram’,‘Dozen ’);

65. Retrieval using LIKE List all Customers whose name starts with ‘A’ and has ‘l’ as the second character OR SELECT CustomerId,CustomerName FROM Customer WHERE CustomerName LIKE ‘Al%’; SELECT CustomerId,CustomerName FROM Customer WHERE CustomerName LIKE _a%’;

66. Retrieval using IS NULL List customers whose date of registration is not available. OR SELECT CustomerId,CustomerName FROM Customer WHERE DateOfReg IS NULL; SELECT CustomerId,CustomerName FROM Customer WHERE DateOfReg IS NOT NULL;

67. SQL - Sorting your results (ORDER BY) List the Items of the retail application in the increasing order of their unit price by default the order is ASCENDING SELECT ItemId , ItemName FROM ITEM ORDER BY UnitPrice ;

68. Retrieval using ORDER BY List the items of the retail application in the decreasing order of their quantity in hand. SELECT ItemId , ItemName FROM ITEM ORDER BY 3 DESC;

69. Retrieval using ORDER BY List the items in their decreasing order of quantity on hand and increasing order of discount. SELECT ItemId , ItemName , QtyOnHand ,Discount FROM ITEM ORDER BY QtyOnHand DESC, Discount;

70. SELECT statement will retrieve those particular rows where 'country' is the USA. mysql > SELECT * FROM publisher -> WHERE country='USA';  particular rows where country and city of the publisher are 'USA' and 'New York'. mysql > SELECT * FROM publisher WHERE country='USA‘ AND pub_city ='New York'; mysql > SELECT pub_name,country,pub_city -> FROM publisher -> WHERE country='USA' OR pub_city ='New York' SELECT pub_name , country,pub_city FROM publisher ORDER BY pub_name ; ORDER BY pub_name in descending order mysql > SELECT pub_name,country,pub_city -> FROM publisher -> ORDER BY pub_name DESC; SELECT * FROM publisher WHERE name LIKE ' N__e %'; write a SQL query to find the details of those publisher name whose names begin with ‘N’ and the fourth character is ‘e'.