Monday, 3 October 2016

IP MYSQL Tutorial/Help- By - Angad Soni


Download Mysql from Mysql.org
you can also visit www.angadsoni.westarts.com
to download mysql software down the page.
<<<< Take a look at Being Technise Startup.>>>>>>

You can also download mysql from my website www.angadsoni.webstarts.com from down the page.

 will be posting a video on my youtube channel (SOON) regarding all this and  also about shortcut keys +variable name+easy going programming for all of you.
Start learning...................
(<<< Do take look To my another blog on NETBEANS PROGRAMMINg>>>)
<<< Getting installed and famalier with mysql/////

Installing and Starting MySQL

There are different ways to install MySQL. The following covers the easiest methods for installing and starting MySQL on different platforms.

<<((   For FULL AND MORE HELP VISIT http://adf.ly/1eXEUa  ))>>
                             

<<Before starting I would like to tell you that this is only short notes for berif illustration do refer to SUMITA ARORA or IP book by Ncert.>>


                                                                           










Then install it and start learning..................
How Mysql was started .. History??
It was created and supported by MySql AB, a company based in Sweden.This company is now a subsidiary of Sun Microsystem. On April 20th 2009 Oracle corp . announced a deal to acquire Sun Microsystems.
MySql was invented by Michael Widenius. MySql has been named after Monty’s daughter My. The logo of MySql, the dolphins, is named as “Sakila”.

 Server:It is a machine which listen clients request and respond according to it and return content from database which is required by client.

 Client:They are the machine send queries to database in pre-defined format.

 Data:Raw facts are known as data that doesn’t conclude result.

 Database:Collection of data in the form of tables.

 Tables:Collection of Record.

 Records:Collection of columns.

 Cardinality: No. of Records in a table.

 Degree: No. of Columns in a table.

 Keys: They are the fields or columns.
Primary key: It is used to uniquely identify a record in a table. It must of not null,it must be unique.
Candidate Key: These are the columns or keys having abiliy to become primary key.
Alternate Key: After making one column primary key from candidate keys, and the left over candiate keys are known as Alternated key
Foreign Key: This column is used to establish relationship between two tables.This column must contain values derived from primary key of some other column.It may contain null values.
Candiate Key
ROLLNO ADMNO REGNO NAME ADDRESS
121 A1 R1 Amit Jawahar Nagar
231 A2 R2 Anil Malviya Nagar
342 A3 R3 Sohan Jhotwara
                                   
Primary Key      Alternate Key
                               
Classification of MySql Statements:
We can classify SQl in different parts as per their purposes. They can be classified in following categories:
DDL:Data Definition Language: Use to define or redefine the structure of Database and its objects.
Commands are: Create, Alter, Drop
DML:Data Manipulation Language:Use to manipulate(change) values in the table
Commands are: Insert, Update, Delete,Select
TCL:Transaction Control Language:Use to control Transactions.
Commands are: Start Transaction, Rollback, Savepoint, Commit
DCL:Data Control Control:Use to control Privelages over the database.
Commands are:Revoke, Grant
DataTypes
In MySql we have three major categories:
        Data Types

   
          1.  Numeric                  2.  Date  and Time                       3. Text  

Numeric :   Int-A normal-sized integer that can be signed or unsigned. If signed, the allocate range is from -2147483648 to 2147483647. If unsigned, the allwable range is from 0 to 4294967295. Size is upto 11.
Decimal(M,D) –An unpacked floating point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one bytes. Defining the display length (M) and the number of decimal(D) is required.
Date: Date:A date is YYYY-MM-DD format between 1000-01-01 and 9999-12-31.
DateTime:A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.
Text: Char(M)-A Fixed- length string between 1 and 255 characters in length, right padded with spaces to the specified length when stroed.Defining length is not requires, but the default is 1.
Varchar(M)-A variable length string between 1 and 255 characters in length, you must define a length when creating a varchar field.
DIFFERENCE BETWEEN CHAR AND VARCHAR
Char Varchar
Fixed length  Variable length
Memory wastage No memory wastage
No need to specify size, by default it will take size of 1 You must specify size
Ex: name Char(10)
A M I T X X X X X X
Inabove example we had taken size of 10 characters for name field, but we are using 4 characters for AMIT and remaining 6 are padded with name so this memory get wasted. Ex: name Varchar(10)
A M I T
In above example we had taken size of 10 characters for name field, but we are using 4 characters for AMIT and remaining 6 are again go back to main memory.

Comments: These are the statements which are not executed and they are used only for documentation purpose.
Single line comments:-Statements started with # or --.
Multi line comments: Statement start with /* and ends with */
Referential Integrity: When we are going to insert value in foreign key these values must follow some kind of rules like they must have same values and data type as primary key. Foreign key accepts repeated as well as null value.
Constraints: They are some type of rules and regulations which we have to follow when we are going to insert record in a table.
Primary Key: It is used to uniquely identify a record inside the table. Value must be unique and not null.
Check: It is used to check whether the entered value fulfill the condition or not, if condition return true than value can be entered in a table, if condition return false than value cannot be entered.
Default: This constraint is used to give default value, if user forget to enter value in some column or he/she don’t want to enter value than by default value some value will be entered in the concern column.
Unique: By this constraint we can enter only unique value in a column, if user enter repeated value than this will show error.
Not Null:  By this we cannot enter null value in a column.
Enum. It will provide set of values from which user select some value and enter to the record.
Foreign Key: This column is used to establish relationship between two tables.This column must contain values derived from primary key of some other column.It may contain null values.



Visit : www.angadsoni.webstarts.com
Take a look at my youtube channel : Being Technised




Check out my web: Angadsoni.com


Commands
1. USE
This command is use to open an existing (already created) database.
Syntax: use <DATABASE NAME>;
Eg: use ishwar;// This will open database ishwar


2. SHOW
This command is use to show list of all databases present in computer.
Syntax: Show Databases;

3. CREATE
This command is used to create database and its object i.e., Databases and tables
Syntax: Create database ishwar;//This will create database with Ishwar;


Create table <tablename>(Column name1 datatype (constraints), Column name2 datatype (constraints)……);
Eg: Create table student(rollno int, name varchar(30), Address varchar(30));

4. INSERT
This command is used to insert values (record) in a table.
Syntax: Insert into <table name> (column name1, column name2…) values    (value1, value2….);
Eg: Insert into student(rollno,name,address)values(101,’Amit’,’Vaishali Nagar’);
Insert into student values(102,’Radhyemohan’,’Malviya Nagar’);
Insert into student values(103,’Amit’,’Jhotwara’);
Insert into student values(104,’Rajendra’,’Jawahar Nagar’);
 
5. SELECT
This command is used to retrieve value from table.

Clauses used in SELECT command.
1. select *from student;
It will retrieve all columns from table student.

2. Select columnname1, columnname2 from student;
It will retrieve columnname1 and columnname2 from table student only.
Eg-> select name, address from student;


3. Select * from student where condition;
Eg-> Select *from student where rollno=101;

This will retrieve all columns of record whose rollno is 101.

4. DISTINCT:It is used to return non repeated values from a colulmn or you can it will remove redundant values.
Select Distinct columnname from student;
Eg-> Select distinct name from student;


This will return non repeated name from name column, you can see in above select statements there are two Amit but in this Distinct clause statement only one Amit is there.

5. ORDER BY: It is used to show data on sorted manner either in ascending or descending order.
Eg->Select name from student order by name;

 If you do not specify asc or desc than by default it will use ascending(asc).
Select name from student order by rollno desc;

This will return name from student on the basis of descending order of rollnos.


<Do take a look to my website : http://www.angadsoni.webstarts.com
Follow me on facebook : fb.me/being.angadsoni
follow me on twitter :  www.twitter.com/angad57  >


6. BETWEEN:  It is used to retrieve data from table which follow in some certain range. It replace relational AND operator.
Eg-> Select name,rollno from student where rollno Between 101 and 105;



Eg-> Select name,rollno from student where rollno>=101 AND rollno<=105;

This wills gives you name of all students having rollno from 101 to 105.You can see output of above both command give same result.
7. IN: It is used to retrieve data from table where data matches with values given in list. This replace Relational OR operator. Eg-> Select name,rollno from student where rollno IN(101,103,105);

Eg-> Select name,rollno from student where rollno =101 OR rollno=103 OR rollno=105;


In above two screen shots you can see both generate same output.
8. LIKE:  It is used for pattern matching where we have to check some condition with one or two characters in some word. We use two wildcard characters % and _.
% is used when we don’t know about characters and their position in word.
_ is used when we know position of character in word.
Ex-> Select name from student where name LIKE‘%a%’; // it will return name of student who contain ‘a’ anywhere in name.

Ex-> Select name from student where name LIKE ‘%a’; // it will return name of student who contain ‘a’ at end in name.

Ex-> Select name from student where name LIKE ‘a%’; // it will return name of student who contain ‘a’ at beginning.

Ex-> Select name from student where name LIKE ‘_a%’; // it will return name of student who contain ‘a’ at second position.

Ex-> Select name from student where name LIKE ‘%a_’; // it will return name of student who contain ‘a’ at second last position.


9. IS NULL: It will return values of the column where record contain NULL value. When we are going to check whether the column contains NULL value or not we cannot use = sign we have to use IS.
Ex-> select name from student where address= NULL;// this will gives you error
Right answer is -> select name from student where address IS NULL
10. IS NOT NULL: It will return values of the column where record doesn’t  contain NULL value. When we are going to check whether the column contains  NOT NULL value or not we cannot use = sign we have to use IS.
Ex-> select name from student where address= NULL;// this will gives you error
Right answer is -> select name from student where address IS NOT NULL



6. UPDATE
This command is use to change values in the table with SET clause. If we want to change some value or by mistaken we entered some value and now we want to correct it , that time we should use Update command.
Ex-> Update student set name=”Udai” where name =”Amit”;
In above query it will change name of Amit to Udai .

Ex-> Update student set fees=fees+500;
In above query you will see Where clause is missing, that’s why it will increase fees of all students by 500.

7. ALTER
This is a DDL command. It is used to modify structure of Database and tables.
Following clauses are used in Alter command
ADD- This clause is used to add new column or Primary key in a table.
Ex->Alter table student ADD Fees int;


Above command will add new column Fees in student table of int.
Ex-> Alter table student add primary key(rollno);

If we don’t have any primary key in table than above command will make rollno as primary key.
DROP-> This clause is used to remove column from table.


8. DELETE
This command is used to delete records from given table. It will delete only values not structure.
If you want to delete record of some particular student.
Ex->Delete from student where name=’Udai’;

If you want to delete complete records from the table
Ex-> Delete from student;


9. DROP
This command is used to remove table or database along with their structure.
Ex-> Drop database ishwar;
This will remove database ishwar from your computer
Ex-> Drop table student;
This will remove table student from your computer.











Functions
They are special type of code written for specific task to be performed. In this class we will learn two type of functions, 1.Single Row Functions 2. Aggregate functions.
Single Row functions are classified in three categories 1.Numeric functions 2. String Functions 3.Data and Time functions.
1.Numeric Functions:
a. Pow/Power
This method is used to calculate mn. Arguments for this function can be value or column name.
Ex-> Select Power(2,3);

b. Round
This method is used to round off the given value, arguments for this function can be value or column name.


c. Truncate
This method is used to truncate value from a specified value.



2. String Functions: String is a group of characters and the first character of string having position 1 not 0 as JAVA.
a. Concat: This method is used to concat two or more strings provided as arguments to this method. This method also take column of table as arguments.

b. Substring/Substr/Mid: This method is used to extract a part of string from a given string, we have to provide string,starting position and no. of characters that we want to extract from string. You can also give column name instead of string.

In first example when we give only string and one numeric value than this numeric value serve as starting position from where we have to extract string.
In second example where we specify string as well as two numbers than these two numbers serves as starting position(2) and from second position you have to extract 4 characters.
In third example where we specify string as well as one negative that signify, we have to 7 characters from right and then extract 4 characters towards end.
c. instr:  This method is used to return position of a given substring in  a given main string. It will accept column names as arguments.

In above queries you will see the position of ‘jas’ in Rajasthan in First query i.e., 3 and in second query 0 because ‘at’ is not present in ‘ Rajasthan’. This method will return first appearance of the substring in main string.
d Trim:  This method is used to remove spaces from beginning and end of string. This will not remove space between two words.

e. ltrim: This method is used to remove spaces from beginning of string. This will not remove space between two words.

f rtrim: This method is used to remove spaces from end of string. This will not remove space between two words.

g left: This will extract  specified no. of characters from left side

h right: This will extract  specified no. of characters from left side

I lcase: This method is used to convert uppercase string in lower case,it will accept string or some column name as argument.

J ucase: This method is used to convert lower case string in upper case, it will  accept string or column name as argument.

k. length: This method is used to count no. of characters in a given string, It count all special symbols even space also.


3.DateTime: Date methods operate on values of the Date types.
a. Curdate: This function returns current date.
 

b. Date:This function extracts the part of a date or datetime expression.


c. Month: This method returns the month from date passed.
 
d. Year. This function returns the year part of the date.

e. Dayname: This function returns the dayname of weekday.

f. Dayofmonth: This function returns the day of month.

g. Dayofweek: This function returns the day of week.

h. Dayofyear: This function returns the day of the year.

i. Now: This function returns the current date and time.

j. Sysdate:This mehod returns the time at which the function executes.

DIFFERENCE BETWEEN SYSDATE() AND NOW()
This can be explained with the help of below written example.

In above example you can see, when we execute first query  both sysdate give us different time, there is difference of 10 seconds given in sleep method.This means sysdate gives you self execution time, where as in second query in which there are two nows ,both now methods gives same time after having sleep method that means both methods give execution time of query not their own.


GROUPING FUNCTIONS
Aggregate Functions: These are the functions which return single row based on group of rows.
Some aggregate functions are:
AVG( )
COUNT( )
MAX( )
MIN( )
SUM( )
1. Avg( ): Returns the average values of a specified column.


2. Count( ):Returns the total number of values in the specified field.

In above two queries you will see both Count method gives two different output.
In first query * will count all records present in table, that means it count all Null values also.
In second query when we specify column name than it will not count Null value.

3. Max( ): The max functions returns the largest value from the selected column.


4. Min(): This method returns the smallest value from the selected column.


5. Sum(): This method is used  to sum of values in the specified column, it will exclude NULL values, and add only numeric values.

In next example you will see when we add NULL to some numeric value than it will be return NULL value.
If we concat string value to NULL than also it will return us NULL. But in aggregate methods Null do not works as NULL, and it will be excluded from calculations.


 Group By clause
This clause is useful when we want to find output or some result of aggregate functions on the basis of value of some particular column. First we have to group common values and than find output.


GROUP BY USING MIN FUNCTION

In above query first it make group and than find minimum out of that group.

GROUP BY USING MAX FUNCTION

In above query first it make group and than find maximum out of that group.

GROUP BY USING COUNT FUNCTION

GROUP BY USING COUNT WITH DISTINCT CLAUSE

In above example first we group zone than distinctly count all the zones.

HAVING CLAUSE
When we want to put condition on some aggregate function that time we use HAVING clause.



DIFFERENCE BETWEEN HAVING AND WHERE
Where clause is used to put condition on column while having is used to put condition on aggregate function conjunction with Group by clause.

Cartesian Product
The Cartesian product is also known as cross join. The degree of new table(relation) is the sum of the degree of two tables(relations),the cardinality of new table would be multiplication of nos. of rows of both two tables.

Table – Student
RollNo Name
1 Amit
2 Anil
3 Sunil

DEGREE- 2
CARDINALITY-3

Table-Marks
RollNo Marks CGPA
1 90 8
2 98 9
3 78 7

DEGREE-3
CARDINALITY-3

SELECT * FROM STUDENT,MARKS;
Result:
RollNo Name RollNo Marks CGPA
1 Amit 1 90 8
2 Anil 1 90 8
3 Sunil 1 90 8
1 Amit 2 98 9
2 Anil 2 98 9
3 Sunil 2 98 9
1 Amit 3 78 7
2 Anil 3 78 7
3 Sunil 3 78 7

DEGREE: 2+3=5 (Columns)
CARDINALITY: 3 X 3=9(Rows)

This Problem can be resolve with the help of JOINS and here we are discussing only EQUI-JOINS

EQUI JOINS
Equi join is a simple sql join condition that used equal sign as a comparison operator.
Pid Lastname Firstname City
1 Sharma Abhay Mumbai
2 Gupta Mohan Delhi
3 Verma Akkhil Mumbai

Oid Orderno Pid
1 10050 3
2 25000 3
3 5687 1
4 45000 1
5 35000 15

EX: Select lastname,firstname,orderno from person,orders where person.pid=orders.pid order by person.lastname;



The query will give the following result
Lastname Firstname Orderno
Sharma Abhay 5687
Sharma Abhay 45000
Verma Akhil 10050
Verma Akhil 25000

































Page No.33
1. USE LIBRARY;
2. i. Mname Hirav Rajeev
ii. Mname Sheetal
Rajeev
3. Rows-8
Columns-3
4. Keys having ability to become primary key are known as candidate key, a table can have multiple candidate key, After selecting one candidate as primary key and all left over candidate keys are known as Alternate key.
5. SELECT *FROM RESULT WHERE GRADE IS NULL;
6. SHOW DATABASES;
7. DELETE command is used to remove records.
8. SELECT NAME,CLASS FROM STUDENTS WHERE STREAM_NAME IS NULL OR STREAM_NAME LIKE ‘%COMPUTER%’;
9. She should use DROP command which is used to remove structure of table.
10. Drop command is DDL command where Delete command is DML command.DROP command is used to remove structure of table along with data while DELETE command is used to remove records only and structure of table remain in database.
11. i. SELECT NAME FROM RESULT WHERE DIVISION =’FIRST’ ORDER BY NAME ASC;
ii. SELECT NAME,SUBECT,STIPEND * 12 AS “ANNUAL STIPEND” FROM RESULT;
iii. SELECT COUNT(NAME) FROM RESULT WHERE SUBJECT=’ACCOUNTS’ OR SUBJECT=’INFORMATICS’;
iv. INSERT INTO RESULT VALUES(7,’MOHAN’,500,’ENGLISH’,73,’SECOND’);
v. AVG(STIPEND) 475
vi. COUNT(DISTINCT SUBJECT ) 6
vii. MIN(AVERAGE) 38

12. ALTER command is DDL command which is used to redefine the structure of table that consist of adding column or constraint, modify column definition, dropping of column and constraint from table. It is different from UPDATE command as UPDATE command is DML command which is used to change only values present in table.
13. GROUP BY clause is used to make group of similar values present in a column of a table, after that we can perform aggregate functions on that group. It is different from ORDER BY clause as it is used to show data in sorted order either in ascending or descending.
14. Cardinality-4 Degree-5
15. i. SELECT PNAME FROM SUPPLIER WHERE PNAME LIKE’B%’ ORDER BY PRICE;
ii. SELECT SCODE ,PNAME,CITY FROM SUPPLIER WHEREQTY<150;
iii. SELECT COUNT(DISTINCT CITY)FROM SUPPLIER;
iv. INSERT INTO SUPPLIER VALUES(110,’BOURNVITA’,’ABA’,170,’DELHI’,40.00);
v.          PNAME
MAGGI
vi. COUNT(DISTINCT CITY) 3
vii. MAX(PRICE) 55.00
Page No.35
1. DBMS- Stands for DataBase Management System used to store and manage data,it includes creation, insertion, updation, retrieval of data. 
2.
3.
4.
5.
6. In this data is organized in the form of tables, these tables are known as Relation. A row represents relationship between set of values.
7. Referential integrity is  a set of rules which we have to follow when we are going to set relationship between records of two valid tables, and that users don’t accidentally change or delete data.Rules are
-> Foreign key of foreign table must be related with primary key field of some other table.
-> Above mentioned both fields have same data type.
-> Both tables must exist in same database.
-> Values of foreign key must be derived from primary key values.
-> Foreign key can contain null value.
-> You can’t delete record from primary key table if their related date exists in related table.
-> You can’t change primary key value in primary table if that record has related record.
8. A non-key attributes whose values are derived from primary key of some other table of same database.
9. Advantage of DBMS:
i. Redundancy can be avoided
ii. Inconsistency can be controlled
iii. Data can be shared
iv. Security measures can be implemented.
10. Disadvantages of DBMS:
i. Skilled persons are required
ii. Computer systems are required which create expenses.
iii. Threats of Computer viruses.
11. TNO-varchar
TNAME-varchar
TADDRESS-varchar
SALARY-int
DEPT_NO-int
DOJ-Date
Dept_No-int
DeptName-varchar
12. HAVING clause is used with conjunction of GROUP BY clause where it is used to check condition on aggregate function where as WHERE clause is used to check condition on column values.
13.
14. Functions can be broadly categorized in two categories: Single row functions and Aggregate functions. In single row function we have numeric functions, char functions and date and time functions.
15. concat( ),substr( ),instr( ),trim( ),upper ( ) lower( ) etc.
16. round( ),truncate( ),pow( ),mod( )
17. mod( )
18. lcase ( ) or lower
19. length( )
20. 1
21. substr( ), right( ), left( )
22. ltrim( ), rtrim( )
23. 123.57
24. length ( )
25. curdate ( )
26. SELECT LEFT(‘INFORMATION’,3);
SELECT SUBSTR(‘INFORMATION’,1,3);
27. SELECT ROUND(AMOUNT,1);
30310.0
SELECT ROUND(AMOUNT,0);
30310
SELECT ROUND(AMOUNT);
30310
28. SELECT ROUND(CNT,0);
35676
SELECT ROUND(CNT);
35676
SELECT ROUND(CNT,3);
35675.877
29. SELECT LTRIM(P),RTRIM(P);
30. a. 124.87
b. 101.000
c. SELECT TRIM(NAME),TRIM(ADDRESS) FROM PASSMAST;
d. SELECT LCASE(‘ABC INTERNATIONAL PUBLIC SCHOOL’);
e. SELECT CURDATE( );

31. 1. CREATE TABLE LOAN_ACCOUNTS(ACCNO INT PRIMARY KEY,CUST_NAME VARCHAR(30), LOAN_AMOUNT INT, INSTALMENTS INT, INT_RATE DECIMAL(4,2), START_DATE DATE, INTEREST DECIMAL(5,2));
2. DESC LOAN_ACCOUNTS;
3. INSERT INTO LOAN_ACCOUNTS VALUES( 1,’R.K.GUPTA’,30000,36,12.00,’2009-07-19’,NULL);
4. SELECT *FROM LOAN_ACCOUNTS;
5. SELECT ACCNO,CUST_NAME,LOAN_AMOUNT FROM LOAN_ACCOUNTS;
6. SELECT *FROM LOAN_ACCOUNTS WHERE INSTALMENTS <40;
7. SELECT ACCNO,LOAN_AMOUNT  FROM LOAN_ACCOUNTS WHERE START_DATE<’2009-04-01’;
8. SELECT INT_RATE FROM LOAN_ACCOUNT WHERE START_DATE>’2009-04-01’;
9. SELECT *FROM LOAN_ACCOUNTS WHERE INTEREST IS NULL;
10. SELECT *FROM LOAN_ACCOUNTS WHERE INTEREST IS NOT NULL;
11. SELECT DISTINCT(LOAN_AMOUNT) FROM LOAN_ACCOUNTS;
12. SELECT DISTINCT(INSTALMENTS) FROM LOAN_ACCOUNTS;
13. SELECT *FROM LOAN_ACCOUNTS WHERE START_DATE >’2008-12-31’ AND INSTALMENTS > 36;
14. SELECT CUST_NAME ,LOAN_AMOUNT FROM LOAN_ACCOUNTS WHERE INSTALMENTS != 36;
15. SELECT CUST_NAME, LOAN_AMOUNTS FROM LOAN_ACCOUNT WHERE LOAN_AMOUNT<500000 OR INT_RATE >12;
16. SELECT *FROM LOAN_ACCOUNTS WHERE YEAR(START_DATE)=’2009’;
SELECT *FROM LOAN_ACCOUNTS WHERE START_DATE >=’2009-01-01’ AND START_DATE <=’2009-12-31’;
17. SELECT *FROM LOAN_ACCOUNTS WHERE LOAN_AMOUNT >=400000 AND LOAN_AMOUNT<=500000;
18. SELECT *FROM LOAN_ACCOUNTS WHERE INT_RATE >=11 AND INT_RATE<=12;
19. SELECT CUST_NAME,LOAN_AMOUNT FROM LOAN_ACCOUNTS WHERE INSTALMENTS IN(24,36,48);
20. SELECT *FROM LOAN_ACCOUNTS WHERE LOAN_AMOUNT BETWEEN 400000 AND 500000;
21. SELECT *FROM LOAN_ACCOUNTS WHERE INT_RATE  BETWEEN 11 AND 12;
22. SELECT ACC_NO,CUST_NAME,LOAN_AMOUNT WHERE CUST_NAME LIKE’%SHARMA’;
23. SELECT ACC_NO,CUST_NAME,LOAN_AMOUNT WHERE CUST_NAME LIKE’%A’;
24. SELECT ACC_NO,CUST_NAME,LOAN_AMOUNT WHERE CUST_NAME LIKE’%A%’;
25. SELECT ACC_NO,CUST_NAME,LOAN_AMOUNT WHERE CUST_NAME NOT LIKE’%P%’;
26. SELECT ACC_NO,CUST_NAME,LOAN_AMOUNT WHERE CUST_NAME LIKE’%A_’;
27. SELECT *FROM LOAN_ACCOUNTS ORDER BY LOAN_AMOUNT ASC;
28. SELECT *FROM LOAN_ACCOUNTS ORDER BY START_DATE DESC;
29. SELECT *FROM LOAN_ACCOUNTS ORDER BY LOAN_AMOUNT ASC,START_DATE DESC;
30. UPDATE LOAN_ACCOUNTS SET INT_RATE =11.5 WHERE INT_RATE IS NULL;
31. UPDATE LOAN_ACCOUNTS SET INT_RATE=INT_RATE +INT_RATE *0.5 WHERE LOAN_AMOUNT>400000;
32. UPDATE LOAN_ACCOUNTS SET INTEREST= (LOAN_AMOUNT*INT_RATE*INSTALMENTS) / 12 * 100;
33. DELETE FROM LOAN_ACCOUNTS WHERE START_DATE<’2007-01-01’;
34. DELETE FROM LOAN_ACCOUNTS WHERE CUST_NAME=’K.P.JAIN’;
35. ALTER TABLE LOAN_ACCOUNTS ADD CATEOGRY CHAR(1);
36. SELECT MAX(LOAN_AMOUNT) FROM LOAN_ACCOUNT WHERE INT_RATE=12;
37. SELECT CUST_NAME, MIN(LOAN_AMOUNT) FROM LOAN_ACCOUNT WHERE YEAR(START_DATE)=’2009’;
38. SELECT AVG(LOAN_AMOUNT)FROM LOAN_ACCOUNTS;
39. SELECT SUM(LOAN_AMOUNT) FROM LOAN_ACCOUNTS WHERE YEAR(START_DATE)=’2009’;
40. SELECT COUNT(INT_RATE) FROM LOAN_ACCOUNTS WHERE INT_RATE IS NULL;
41. SELECT COUNT(CUST_NAME) FROM LOAN_ACCOUNTS WHERE START_DATE>’2008-12-31’;
42. SELECT CUST_NAME,LENGTH(CUST_NAME),LCASE(CUST_NAME),UCASE(CUST_NAME) FROM LOAN_ACCOUNTS WHERE INT_RATE<11.00;


43                            
44.

45.
 


46.

32. 1. POW(4,3)         POW(3,4)
64 81

2. ROUND(543.5694,2) ROUND(543.5694) ROUND(543.5694,-1)
543.57 544 540      

3. TRUNCATE(543.5694,2) TRUNCATE(543.5694,-1)
543.56 540

4. LENGTH(‘PROF.M.L.SHARMA’);
15

5. FULLNAME
SHEIKHHAROON

6. YEAR(CURDATE()) MONTH(CURDATE()) DAY(CURDATE())
2016 09 15

7. DAYOFYEAR(CURDATE())     DAYOFMONTH(CURDATE())        DAYNAME(CURDATE())
259 15 THURSDAY

8. LEFT(‘UNICODE’,3) RIGHT(‘UNICODE’,4)
UNI CODE

9. INSTR(‘UNICODE’,’CO’) INSTR(‘UNICODE’,’CD’)
4 0

10. MID(‘INFORMATICS’,3,4) SUBSTR(‘PRACTICES’,3)
FORM ACTICES
SUMITA ARORA
PAGE NO-423
1. MySql  server is machine which is used to perform query as per client requrest.Clients are the machines which send request to server and server respond in pre defined format.
2. SQL (Structure Query Language) is a language which is used to communicate with  database.It is categories in 4 categories they are-DDL,DML ,DCL and TCL
3. Data dictionary consist of meta data i.e., data about data.
4. Text-4000 bytes  int-53 digits
5. Datatypes signifies that what kind of values user wants to enter in table. Examples are int,tinyint,decimal,date,char,varchar etc.
6. When no values entered by user than by default null values has been entered in table. No, if we use null in arithmetic expression then we’ll get null value only.
7. DISTINCT
8. SELECT CURDATE( );
9. SELECT 12*15;
10. Function are set of instructions used to perform some specific operations on given arguments.
11. a. InormaticsPractices
b. informatics practices class 11th
c. COMPUTER STUDIES
d. classXII
12. a. MySql is a open source RDBMS used to create or manage database.
b. No,because 0 is an integer value while NULL doesn’t have any data type and when we use Null in arithmetic expression then we’ll get NULL.
c. UPDATE COMPANY  SET COMM=COMM+500 WHERE SALES>200000;
d. % and _ both are used with like operator for pattern matching but the difference between both these is we use % when we don’t know number and position of characters and _ when we know the position and number of characters.
e. Char and varchar both are used to store alphanumeric values given by user ,and the difference is no need to specify size in char and it will assume by default size of 1 character while  in varchar  we must give size.

13. SELECT ‘TODAY, THE DATE IS’,CURDATE( );

14. a. ALTER TABLE STUDENT MODIFY FEES INT NOT NULL;
b. SAVEPOINT A1;
c. Foreign key is a non key attribute used to establish relationship between two tables.
d. Cardinality-8 Degree-6
e. First query consider null value that means count(*)count null value while count(hobby) doesn’t count null value.

15. a. use company;
show tables;
b. Alter table  student drop hobbies;
c. INSERT INTO EMP (EMPNO,SALES)VALUES(100,200.50);
d. Cardinality-20  Degree-5
e. i.DROP ii.DESC | DESCRIBE

16. a. SHOW CREATE TABLE <TABLE NAME>;
b. Savepoint in transaction work as bookmark that means where we put savepoint all the  changes made by sql commands above savepoint will be saved.
Start transaction
Update student set fees=fees+500;
Savepoint A;
c. CURDATE ( ) function is used to return current date of system, while DATE( ) function is used to return  date present in some expression.
d. Cardinality-8 Degree-5
e. First query consider null value that means count(*)count null value while count(salary) doesn’t count null value.
17. Constraints are the rules and conditions enforced at the time of table creation, and when we enter values in table that time our values must follow all those condition than only values can enter in table.
Primary key, Not Null, Unique, Check, Default, Foreign Key
18. Primary and Unique key both contain non repeated value but the difference is unique key can contain null value while primary key doesn’t contain null value.
19. Primary key is a field or column which is used to uniquely identify a record in a table. This column must be not null and must be unique.
20. When we make a column not null that means we must enter some value in column and we can’t left that column blank.
By default constraint we mean that if user not enter any value in a column than a value which is specified with default constraint has been entered in that column.
21. NULL
22. Statement incomplete.
Update table set columnnmae=value;
23. DELETE FROM EMPL;
24. SHOW DATABASES;
25. DELETE
26. a. Order by clause is used to show data in sorted order either in ascending or descending order. It is different from Group by clause because Group by clause is used to make group of similar values present in a column and after that performing some aggregate functions on them.
b. Cardinality-4 Degree-3
27. USE CONTACTS;

PAGE NO:425 SHORT ANSWER QUESTIONS
1. Database server is a machine which is used to listen queries send by database client and respond to those queries as per predefined format.
MySql is an open source RDBMS used to manage database with the help of SQL.
2. SQL is categorized in three classes:
DDL ,DML,DCL,TCL
3. DDL statements are used to define or redefine the structure of database and its objects while DML statements are used to manipulate values in the table.

                << ....... This content is a copy of iphelpbyishwarskhangarot.blogspot.com's page but is                                                        slightly modifies for easy understanding ................>>
CHECK OUT  netbeans blog ....... as    :    ipnbeans.blogspot.com


and also mysql and netbeans JDBC connection to make any project with connectivity sample will be posted soon as ipjdbc.blogspot.com (at present doesn't exists)


Posted by at
Labels: , , , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)