SQL QUERIES

 SQL COMMANDS/QUERIES

 TO INSERT RECORDS IN THE TABLE

Command: INSERT

Syntax 1:                 

INSERT INTO tablename  

(attribute1, attribute2,…, attributen)

VALUES (value1, value2,… valuen);

Syntax 2:

INSERT INTO tablename

VALUES (value1, value2,… valuen);

 To enter the following record in table STUDENT

ADMNO: 54910      STUDNAME: SUMIT KUMAR       CLASS: IX       SECTION: A

ROLL: 21       ADMDATE: 01/04/2013

 INSERT INTO STUDENT

(ADMNO, STUDNAME, CLASS, SECTION, ROLL, ADMDATE)

VALUES (‘54910’, ‘SUMIT KUMAR’, ‘IX’, ’A’, 21, ‘2013-04-01’);

or

INSERT INTO STUDENT

VALUES (‘54910’, ‘SUMIT KUMAR’, ‘IX’, ’A’, 21, ‘2013-04-01’);

 TO DISPLAY RECORDS FROM TABLE:

Command: SELECT

Syntax:         

All records under all fields

SELECT * FROM tablename;

 All records under selected fields

SELECT attribute1, attribute2,…., attributen  FROM tablename;

 Records under all fields which matches a specific condition

SELECT * FROM tablename WHERE condition;

 Records under specific fields which matches a specific condition

SELECT attribute1, attribute2,…., attributen  FROM tablename WHERE condition;

 QUERIES:

To display all records from table STUDENT:

SELECT * FROM STUDENT;

 To display all records under fields ADMNO, STUDNAME from table STUDENT:

SELECT ADMNO, STUDNAME FROM STUDENT;

 To display records from table STUDENT whose SECTION = ‘C’:

SELECT * FROM STUDENT WHERE SECTION = ‘C’;

 To display records from table STUDENT whose CLASS=’X’ or SECTION = ‘C’:

SELECT * FROM STUDENT WHERE CLASS = ‘X’ OR SECTION = ‘C’;

To display records from table STUDENT whose CLASS=’X’ and SECTION = ‘C’:

SELECT * FROM STUDENT WHERE CLASS = ‘X’ AND SECTION = ‘C’;

 To display records under fields ADMNO, STUDNAME, CLASS from table STUDENT whose SECTION = ‘A’:

SELECT ADMNO, STUDNAME, CLASS FROM STUDENT WHERE SECTION = ‘A’;

 Wild Card Characters: They are used to specify a pattern

% : Signifies a collection of characters

_: Signifies a single character

 To display records from table STUDENT whose STUDNAME starts with ‘A’:

SELECT * FROM STUDENT WHERE STUDNAME LIKE ‘A%’;

 To display records from table STUDENT whose STUDNAME ends with ‘A’:

SELECT * FROM STUDENT WHERE STUDNAME LIKE ‘%A’;

 To display records from table STUDENT whose STUDNAME contains the character ‘A’:

SELECT * FROM STUDENT WHERE STUDNAME LIKE ‘%A%’;

To display records from table STUDENT whose 3RD character of STUDNAME is ‘A’:

SELECT * FROM STUDENT WHERE STUDNAME LIKE ‘_ _A%’;

 TO EDIT RECORDS IN A TABLE:

Command: UPDATE

Syntax:         

UPDATE tablename

SET attributename = new value

WHERE condition;

 Note: If no condition is specified all records will be updated.

 To change the ROLL of the student whose STUDNAME is ‘ANIL’ to 5:

UPDATE STUDENT

SET ROLL = 5

WHERE STUDNAME = ‘ANIL’;

 To change the SECTION of the student whose ADMNO is ‘A0021’ to ‘C’:

UPDATE STUDENT

SET SECTION = ‘C’

WHERE ADMNO = ‘A0021’;

 To change the SECTION of the all student whose CLASS is ‘IX’ to ‘A’:

UPDATE STUDENT

SET SECTION = ‘A’

WHERE CLASS = ‘IX’;

 To change the SECTION of all students to ‘B’:

UPDATE STUDENT

SET SECTION = ‘B’;

 To calculate TOTAL of students in table MARKS (attributes ENG, HIN, MATHS, SC, SSC and TOTAL)

UPDATE MARKS

SET TOTAL = ENG + HIN + MATHS + SC + SSC;

 To increase RATE of all items in table ITEM by 10%:

UPDATE ITEM

SET RATE = RATE + RATE * 0.1;

 TO DELETE RECORDS FROM A TABLE:

Command: DELETE

Syntax:         

DELETE FROM tablename WHERE condition;

 Note: If no condition is specified all records will be deleted.

 To delete the record whose ADMNO is ‘A1234’

 DELETE FROM STUDENT WHERE ADMNO = ‘A1234’

 To delete the record whose STUDNAME is ‘AMIT KUMAR’

 DELETE FROM STUDENT WHERE STUDNAME = ‘AMIT KUMAR’;

 To delete the records whose CLASS is ‘IX’

 DELETE FROM STUDENT WHERE CLASS = ‘IX’;

To delete all records 

 DELETE FROM STUDENT;

 

Popular posts from this blog

ENTREPRENEURSHIP SKILLS - II

 ENTREPRENEURSHIP SKILLS - II 1. What is Society? Society is described as an aggregate of people living together in a community, who associate for various engagements including business. 2. What does an Entrepreneur do? An entrepreneur combines factors in a creative manner in order to generate value for customers and create wealth. 3. What are the positive impacts of Entrepreneurship on Society? The positive impacts of Entrepreneurship on Society are Emphasize economic Growth Fosters Creativity Stimulates Innovation and Efficiency Creates Jobs and Employment Opportunities Solves the problems of the society Encourages welfare of the society   4. What role does Society plays in boosting entrepreneurship? Society plays the following roles in boosting entrepreneurship: Creates needs and demands Provides raw materials Enables financial support Creates a need for education Catalyses policy formation and reform Facilitates networking Supports infrastructure developm...
Read more

SELF MANAGEMENT SKILLS - II

SELF MANAGEMENT SKILLS – II   1. What is Self-management? Self-management, also referred to as self-control is the ability to control one’s emotions, thoughts and behavior effectively in different situations. It also includes motivating oneself and setting goals.   2. What are the Basics of Self-management? The basics of Self-management are Self-awareness Responsibility Time Management Adaptability   3. What is Stress? Stress can be defined as our emotional, mental, physical and social reaction to any perceived demands or threats.   4. What are stressors? These demands or threats which are the reasons for causing stress are called stressors.   5. What is Stress Management? Stress Management refers to the method of managing stress properly.   6. Explain the ABC of Stress Management. The ABC of stress management is as follows: A: Adversity or the stressful event B: Beliefs or the way you respond to the event C: Conseque...
Read more
  WEB APPLICATIONS AND SECURITY   1. What is Computer Accessibility? Computer Accessibility refers to the user friendliness of a computer system for all regardless of their disability.   2. Name the numerous types of impairment that impact computer usage. The numerous types of impairment that impact computer usage are Cognitive impairments and learning disabilities, such as dyslexia, attention deficit-hyperactivity disorder (ADHD) or autism. Visual impairment such as low-vision, complete or partial blindness, and color blindness. Hearing impairment including deafness. Motor or dexterity impairment such as paralysis, cerebral palsy, or carpal tunnel syndrome and repetitive strain injury. 3. What is the function of Sticky Keys? StickyKeys is an accessibility feature to help computer users with physical disabilities, but it is also used by others as a means to reduce repetitive strain   4. What is the use of Filter Keys? FilterKeys is an accessib...
Read more