sqlite3 - Syntex error in sqlite
SQLite tutorial
This is SQLite tutorial. It covers the SQLite database engine, sqlite3 command line tool, and the SQL language covered by the database engine. Table of Contents SQLite SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine. Related tutorials and e-books SQLite Python ebook is an in-depth material on SQLite programming using Python language.
java - MySQL and SQLite differences in SQL
Oracle SQL
Resources Database Systems: The Complete Book by Hector Garcia, Jeff Ullman, and Jennifer Widom. A First Course in Database Systems by Jeff Ullman and Jennifer Widom. Gradiance SQL Tutorial . HAVING Clauses mySQL has a very limited form of HAVING clause. Views mySQL does not support views. Intersection and Set-Difference The INTERSECT and EXCEPT operators of SQL are not supported in mySQL. SELECT DISTINCT * FROM R WHERE EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b); To get the set difference, here is a similar approach using a subquery: SELECT DISTINCT * FROM R WHERE NOT EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b); Note that both these expressions eliminate duplicates, but that is in accordance with the SQL standard. ANY and ALL There are some discrepancies between the standard and how the ANY and ALL operators are used in SQL. SELECT * FROM Sells WHERE price Op Quant(SELECT price FROM Sells); Here, Op is one of the comparisons, and Quant is either ANY or ALL.
SELECT basics - SQLZOO
[edit] Introducing the world table of countries This tutorial introduces SQL. We will be using the SELECT command on the table world: The example shows the population of 'France'. Strings should be in 'single quotes'; Show the population of Germany SELECT population FROM world WHERE name = 'France' SELECT population FROM world WHERE name = 'Germany' result [edit] Per Capita GDP The query shows the population density population/area for each country where the area is over 5,000,000 km2. Show the per capita gdp: gdp/population for each country where the area is over 5,000,000 km2 SELECT name, population/area FROM world WHERE area > 5000000 SELECT name, gdp/population FROM world WHERE area > 5000000 [edit] Small and wealthy Where to find some very small, very rich countries.We use AND to ensure that two or more conditions hold true. The example shows the countries where the population is small and the gdp is high. SELECT name , continent FROM world WHERE population < 2000000 AND gdp > 5000000000
sql - Difference between inner and outer join
ORA-01000: maximum open cursors exceeded tips
Oracle docs note this about ORA-01000: ORA-01000 maximum open cursors exceeded Cause: A host language program attempted to open too many cursors. Action: Modify the program to use fewer cursors. Question: I keep encountering ORA-01000 and can't even create a table Answer: First, you should try increasing your OPEN_CURSORS and take a look at the application to see if/why cursors are staying open. ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH; Furthermore, to resolve ORA-01000, try to close whatever cursors are no longer in use, raise the OPEN_CURSORS parameter within your initialization file, and restart Oracle. Resolving ORA-01000 try setting this OPEN_CURSORS to a higher number.
My answers to SQL exercises for db-class.org /Part 1/
/* Delete the tables if they already exist */ drop table if exists Movie; drop table if exists Reviewer; drop table if exists Rating; /* Create the schema for our tables */ create table Movie(mID int, title text, year int, director text); create table Reviewer(rID int, name text); create table Rating(rID int, mID int, stars int, ratingDate date); /* Populate the tables with our data */ insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming'); insert into Movie values(102, 'Star Wars', 1977, 'George Lucas'); insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise'); insert into Movie values(104, 'E.T insert into Movie values(105, 'Titanic', 1997, 'James Cameron'); insert into Movie values(106, 'Snow White', 1937, null); insert into Movie values(107, 'Avatar', 2009, 'James Cameron'); insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg'); insert into Reviewer values(201, 'Sarah Martinez'); insert into Reviewer values(202, 'Daniel Lewis');
SQL In Simple English - Part I
I heard there is some mathematical stuff in SQL? Yeah.. there are many simple operations that you could do in order to formulate some useful information from a database rather than getting simple records from the database. Here are a few examples of these mathematical operations SELECT AVG(age) FROM people Would return 1 value corresponding to the average age of all the persons that exist in the table people. SELECT AVG(age) FROM people WHERE age>30 You should be able to figure that out yourself.. if not please start reading right from the first article in this series ;-) SELECT MAX(age) FROM people Returns the maximum age among all the persons in the table people. SELECT MIN(age) FROM people Returns the minimum age among all the persons in the table people. SELECT SUM(age) FROM people WHERE age>20 Returns the total sum of all the ages of the persons whose age is above 20 from the table people.
My answers to SQL exercises for db-class.org /Part 2/
/* Delete the tables if they already exist */ drop table if exists Highschooler; drop table if exists Friend; drop table if exists Likes; /* Create the schema for our tables */ create table Highschooler(ID int, name text, grade int); create table Friend(ID1 int, ID2 int); create table Likes(ID1 int, ID2 int); /* Populate the tables with our data */ insert into Highschooler values (1510, 'Jordan', 9); insert into Highschooler values (1689, 'Gabriel', 9); insert into Highschooler values (1381, 'Tiffany', 9); insert into Highschooler values (1709, 'Cassandra', 9); insert into Highschooler values (1101, 'Haley', 10); insert into Highschooler values (1782, 'Andrew', 10); insert into Highschooler values (1468, 'Kris', 10); insert into Highschooler values (1641, 'Brittany', 10); insert into Highschooler values (1247, 'Alexis', 11); insert into Highschooler values (1316, 'Austin', 11); insert into Highschooler values (1911, 'Gabriel', 11); insert into Highschooler values (1501, 'Jessica', 11); .mode column
SQL Social-Network Query Exercises Solutions
EXCEPTSelect distinct h1.name,h1.gradeFrom Highschooler h1,Highschooler h2,FriendWhere h1.ID = Friend.ID1AND h2.ID = Friend.ID2 AND h1.grade <> h2.gradeorder by h1.grade,h1.name For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C. Select h1.name,h1.grade,h2.name,h2.grade,h3.name,h3.grade From Highschooler h1,Highschooler h2,Highschooler h3,Likes L Where h1.ID = L.ID1 AND h2.ID = L.ID2 and L.ID1 not in (Select ID1 FROM Friend where (ID1=h1.ID and ID2=h2.ID) OR (ID1=h2.ID and ID2=h1.ID) ) AND h3.ID in Select F1.ID2 from Friend F1,Friend F2 where ((F1.ID1=h1.ID AND F1.ID2 = h3.ID) OR (F1.ID1=h3.ID AND F1.ID2 = h1.ID )) ((F2.ID1=h2.ID AND F2.ID2 = h3.ID ) OR (F2.ID1=h3.ID AND F2.ID2 = h2.ID)) Question 8 Find the difference between the number of students in the school and the number of different first names. select count(ID) - count(distinct name) Question 9
