Difference between revisions of "INSERT .. VALUES"

From SQLZOO
Jump to: navigation, search
 
(7 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<div class=params>schema:scott</div>
 
<source lang=sql class='tidy'>
 
</source>
 
<source lang=sql class='setup'></source>
 
<h3>INSERT</h3>
 
  <p>The INSERT command is used to add a new row to a table.</p>
 
  <pre>INSERT INTO games(yr, city) VALUES (2012,'London')</pre>
 
  <p>The table is <code>games</code><br/>
 
  The column names are <code>yr</code> and <code>city</code><br/>
 
  Strings in the literal values must be quoted with single quotes.</p>
 
<hr/>
 
<h2>Example</h2>
 
  <table border='1' style=''>
 
  <caption>games</caption>
 
  <tr> <th>yr</th> <th>city</th> </tr>
 
  <tr> <td align='right'>2000</td> <td>Sydney</td> </tr>
 
  <tr> <td align='right'>2004</td> <td>Athens</td> </tr>
 
  <tr> <td align='right'>2008</td> <td>Beijing</td> </tr>
 
  </table>
 
<p>The table <code>games</code> shows the year and the city hosting
 
  the Olympic Games.</p>
 
<p>You want to add the next Olympic games, in the year 2012, which will be held in London.</p>
 
<hr/>
 
<div class='qu'>
 
The INSERT statement adds a new row to the table:
 
<source lang='sql' class='def'>
 
INSERT INTO games(yr,city) VALUES (2012,'London');
 
SELECT * FROM games;
 
</source>
 
  
<source lang='sql' class='ans'>
+
CREATE TABLE USERS
INSERT INTO games(yr,city) VALUES (2012,'London');
+
(
SELECT * FROM games;
+
  UserId INT NOT NULL AUTO_INCREMENT,
</source>
+
  Fname VARCHAR (255),
</div>
+
  Mname VARCHAR (255),
 +
  LName VARCHAR (255),
 +
  PRIMARY KEY (UserId)
 +
  )
  
<p>See also</p>
+
INSERT INTO USERS (Fname,Mname,Lname) VALUES ('Saad','A','Mian');
<ul>
+
  <li>[[INSERT .. SELECT]]
+
</ul>
+
  
<h2>What can go wrong</h2>
+
 
<div class='qu'>
+
http://www.kanzulashya.com/oracle-sql/
Your INSERT statement may break some database rule such as the unique key requirement.
+
In this example there is a primary key on year - that means that there may not be two rows with the same year.
+
If you attempt to add a second row with 2008 for yr then you will get an error.
+
<source lang='sql' class='def'>
+
INSERT INTO games(yr,city) VALUES (2008,'Paris');
+
SELECT * FROM games;
+
</source>
+

Latest revision as of 13:30, 7 April 2014

CREATE TABLE USERS (

 UserId INT NOT NULL AUTO_INCREMENT,
 Fname VARCHAR (255),
 Mname VARCHAR (255),
 LName VARCHAR (255),
 PRIMARY KEY (UserId)
 )

INSERT INTO USERS (Fname,Mname,Lname) VALUES ('Saad','A','Mian');


http://www.kanzulashya.com/oracle-sql/

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense