Posts

Lesson 08( Table Management)

  Alter Table  It is used to change existing table structure such as adding or removing columns attributes etc. Syntax- alter table table_name action, [action2…..] Adding new column Drop a column Rename table describe emp2; We want to add a new column as dept. alter table emp2; → add column dept int NOT NULL → after empid; By default it will add at the end. Drop field Alter table table_name drop column_name alter emp2 → drop column dept; Rename Table Rename old table to new name like rename emp2 to emp6; Drop Table It will remove table drop table table_name; Truncate Table It will clear the table data truncate table emp6; drop table emp6 Index in Mysql An index helps in fast retrieval of data from tables. It is just like a index of book where you first search index to look for contents. All primary key column are in the primary index of the table automatically Syntax- Create index index_name on table_name column_name Deleting Index Create table emp_new3(eid, ename varchar(10))...
Post a Comment
Read more

Lesson 07 (Primary Key & Foreign Key)

  Primary Key/Foreign Key Primary Key Combinagtion of column that uniquely identifies a row in table. Rules for Primary Key Primary key must contain unique value. Primary key can’t be null. There is only one primary key in table. Syntax- Col_name data_type PRIMARY KEY. like roll no in every class. We can define more than one column for primary key. PRIMARY KEY(COL1, COL2,......) PRIMARY KEY in existing table ALTER table(table_name) add PRIMARY KEY(primary key column) use college; create table emp11(empid int PRIMARY KEY, ename varchar(10)); describe emp11; create table emp12 (empid int, ename varchar(10),  → PRIMARY KEY(empid, ename); now you can see by describe command create table emp13 (empid int, ename varchar(10)); alter table emp13 → add PRIMARY KEY(empid); FOREIGN KEY It is a field in a table that matches another field of another table. It is used to maintain referential integrity. A table may have more than one foreign key and each fireign key in the child table may re...
Post a Comment
Read more

Lesson 06 (Managing Database)

  Managing Database Create database - To create a database  create database database_name create database EEC Display database - To display database  show databases   Select database - To select a database   select database database_name select database EEC Removing database - To delete a database  drop database database_name drop database EEC Creating Table in Sql with Constraints (बिशेष शर्त के अनुसार ) Create table emp(empid int, ename varchar(40), salary int); insert into emp values(1, ‘a’, 2000); select * from emp; Various Constraints Not null or null This constraints forces the values of a column to non- null values only (column में value भरना अनिवार्य है ) Syntax- Col_name data_type not null; create table emp1(empid int, ename varchar(40), salary int not null); insert into (empid, ename) values(1, ‘a’); क्योंकि salary के कॉलम को खाली नहीं छोड़ सकते इसलिए error  प्रदर्शित होगी | describe emp; table का structure प्रदर्शित होगा | इसमें आप ...
Post a Comment
Read more

Lesson 05(Modifying Data)

  Modifying Data  (Insert, Update, Delete) Insert command का प्रयोग table में डाटा भरने के लिए किया जाता है | Syntax- insert into table(column1, column2,........) value(value1, value2….) select * from department; insert into department values (8, ‘ICE’); insert into department values (‘temp’, 6); ----error आ जाएगी | insert into department (dname, deptid) values (‘Temp’, 6) हो जायेगा | you can insert multiple rows insert into department values<enter> → (7, ‘A’), → (8, ‘B’);<enter> Insert with SELECT statement  It is used to insert data into table from another table. insert into table_name select col1, col2, …. from table_name; Update Statement Update statement is used to update existing data in a table . Syntax- update table_name set col1=exp1 col2=exp2 where condition We want to change deptname where deptid=7 in dept_temp. update department_temp → set deptname=’Applide Sc.’, → where deptid=7; select * from dept_temp; update dept_temp → set deptname=’test’; sel...
Post a Comment
Read more

Lesson 04 (Order & Group Data)

  ORDER BY CLAUSE (Sort Command) Order  by clause is used to sort the result by the select statement. ये कमांड query के परिणाम को sort करके प्रदर्शित करती है | Syntax- order by column1[ASC|DEC], Column2[ASC|DEC],...... By default ASC (Ascending Order) होता है | student table में से सभी छात्रों की जानकारी नाम के  ascending order में प्रदर्शित करो | select * from student order by sname asc; asc दें या नहीं asc ही होगा लेकिन descending में देने के लिए dec दें | student table में से marks के घटते क्रम में छात्रों की जानकारी प्रदर्शित करो | select * from student order by marks dec; student table में से छात्रों के नाम ascending और उनके marks descending order में प्रदर्शित करो | select * from student order by sname asc, marks dec; faculty table में से qualification के निम्न क्रम से प्रदर्शित करो - M.Tech, Ph.D, B.Tech. select * from faculty order by field(qualification, ‘M.Tech’, ‘Ph.D’, ‘B.Tech’); Group by & Having Clause (Grouping Data) select sum(marks) from student; |---...
Post a Comment
Read more