Lecture Data Types Text varchar Decimal10 1 Decimals

Lecture

Data Types • Text • varchar • Decimal(10, 1) • Decimal(s, d) • The number of digits is specified in s. The digits after the decimal point is specified as d.

Queries ID name price descr 1 soap 78 nmnmnmn 2 shampoo 10 nbnbnb

Database and Table Creation: • Creating database: • Create Database Ecommerce; One Way • • Creating Table: Create Table Products ( ID int (15) not null auto-increment, Name varchar (40) not null, Price decimal (10, 4) not null, Description text , Primary key(ID) );

Continued. . Other Way • Create Table Products ( • ID int (15) not null auto-increment primary key, • Name varchar (40) not null, • Price decimal (10, 4) not null, • Description text );

Insert query • Insert into Products (ID, name, price, decription) values (1, ”shampoo”, 1000, ”dfdfdf”) • Bulk Insertion: • Insert into Products (ID, name, price, decription) values (1, ”shampoo”, 1000, ”dfdfdf”), (2, ”soap”, 1000, ”dfdfdf”) , (3, ”cream”, 1000, ”dfdfdf”) , (4, ”lotion”, 1000, ”dfdfdf”) ; • Shorthand insert query; • Insert into Products values (1, ”shampoo”, 1000, ”dfdfdf”)

Selecting two columns from table: Select id, name from products; Selecting distinct values from table: Select Distinct (column_name) from table; • If you apply distinct on whole table (using *) then all rows will be returned no matter if one or more columns of the table is having same values because as a whole one particular record (e. g all columns related to a single product or user) is always different from (all columns of ) other rows or record. • If applied DISTINCT is used on single column, then repetition will be ignored for this column, values of this column will be displayed only once in the result. • If DISTINCT is applied on two columns, then their combination will be taken into regard, if their combination is unique for a particular record , the record will be displayed in result otherwise it will be ignored

ORDER BY Defeult value for order. By is ASC • Select * from user order by city desc; The above query will display the data of all the column with respect to the descending order of city column. • Select city from user order by name desc; The above query will display the data of only the city column with respect to the descending order of name column.

Some SQL FUNCTIONS • Count Select count(names) city from user; Gives count of values of the name column as an output • Sum() output the sum of all values of selected column • Avg() output the average of all values of selected column • Min()return the minimum value of the column as a result • Max()return the max value of the column as a result

IN Operator Alias • Select avg(price) as avgerageprice from products; Output will be shown under avgerageprice heading • IN Operator • The IN operator allows you to specify multiple values in a WHERE clause allowing you to test whether a specified value matches any value in a list. • The IN operator is a shorthand for multiple OR conditions. E. g. column IN (v 1, v 2, v 3) column = v 1 OR column = v 2 OR column = v 3 • Select * from user where country IN(Pakistan, saudia, )

id name city country 1 ali sarhodha pakistan 2 amna lahore india 3 azka sargodha pakistan

Like Operator • Search for the data starting from s • selct * from tablename where column name like s% • search for particular pattern at any position • selct * from tablename where column name like %ly %

• Select distinct (name, city, country) from table_name;