01: package net.sourceforge.squirrel_sql.fw.sql;
02:
03: /*
04: * Copyright (C) 2007 Rob Manning
05: * manningr@users.sourceforge.net
06: *
07: * This library is free software; you can redistribute it and/or
08: * modify it under the terms of the GNU Lesser General Public
09: * License as published by the Free Software Foundation; either
10: * version 2.1 of the License, or (at your option) any later version.
11: *
12: * This library is distributed in the hope that it will be useful,
13: * but WITHOUT ANY WARRANTY; without even the implied warranty of
14: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15: * Lesser General Public License for more details.
16: *
17: * You should have received a copy of the GNU Lesser General Public
18: * License along with this library; if not, write to the Free Software
19: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20: */
21: public interface GenericSQL {
22:
23: public final static String CREATE_STUDENT = "create table student ( "
24: + " sno integer, "
25: + " sname varchar(10), "
26: + " age integer " + "); ";
27:
28: public final static String CREATE_COURSES = "create table courses ( "
29: + " cno varchar(5), "
30: + " title varchar(10), "
31: + " credits integer " + "); ";
32:
33: public final static String CREATE_PROFESSOR = "create table professor ( "
34: + " lname varchar(10), "
35: + " dept varchar(10), "
36: + " salary integer, " + " age integer " + "); ";
37:
38: public final static String CREATE_TAKE = "create table take ( "
39: + " sno integer, " + " cno varchar(15) " + "); ";
40:
41: public final static String CREATE_TEACH = "create table teach ( "
42: + " lname varchar(10), " + " cno varchar(5) "
43: + "); ";
44:
45: public final static String STUDENTS_NOT_TAKING_CS112 = "select * "
46: + "from student " + "where sno not in (select sno "
47: + " from take "
48: + " where cno = 'CS112'); ";
49:
50: public final static String ANSI_SQL_92_PROCEDURE = "CREATE PROCEDURE TRANSFER (:FROM CHAR(7),:TO CHAR(7),:AMOUNT DECIMAL(7,2)) BEGIN /* remove the amount from FROM account */ UPDATE ACCOUNTS SET BALANCE = BALANCE - :AMOUNT WHERE ACCOUNT_ID = :FROM ; /* add the amount to TO account */ UPDATE ACCOUNTS SET BALANCE = BALANCE + AMOUNT WHERE ACCOUNT_ID = :TO ; END; ";
51:
52: public final static String ANSI_SQL_92_PROCEDURE_READABLE = "CREATE PROCEDURE TRANSFER ( :FROM CHAR(7), "
53: + " :TO CHAR(7), "
54: + " :AMOUNT DECIMAL(7,2) ) "
55: + " BEGIN "
56: + " /* remove the amount from FROM account */ "
57: + " UPDATE ACCOUNTS "
58: + " SET BALANCE = BALANCE - :AMOUNT "
59: + " WHERE ACCOUNT_ID = :FROM ; "
60: + " "
61: + " /* add the amount to TO account */ "
62: + " UPDATE ACCOUNTS "
63: + " SET BALANCE = BALANCE + AMOUNT "
64: + " WHERE ACCOUNT_ID = :TO ; " + " END; ";
65: }
|