分布式數(shù)據(jù)庫sql語句(參考版)
2024-10-22 11:52本頁面
【正文】 } Exercise Write the following queries, based on the following database example Movie(title, year, length, inColor, studioName, producerC) StarsIn(movieTitle, movieYear, strName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert, Worth) Studio(name, address, presC) Classes(class, type, country, numGuns, bore, displacement) Ships(name, class, launched) Battles(name, date) Outes(ship, battle,result) In SQL. 1. Find Sandra Bullock’s birthdate 2. Find all executives worth at least $10,000,000 3. Find all the stars who either are male or live in Malibu stars appeared in movies produced by MGM in 1995? 5. Who is the president of MGM studio? 6. Find the countries whose ships had the largest number of guns. 7. Find the names of the ship with 16inch bore. 8. Find the average number of guns of battleship classes. 。 SQLFreeConnect(conn)。 SQLConnect(conn, , SQL_NTS, avi, SQL_NTS, avipasswd, SQL_NTS)。 SQLAllocConnect(env, amp。 /* database connection */ SQLAllocEnv(amp。 HENV env。 EXEC SQL execute dynprog using :account。 嵌入 SQL續(xù) ? The statement for SQLSTATE – EXEC SQL WHENEVER condition action ? Conditon – Not found no data was found ‘02021’ – Sqlerror an error occurred 舉例 ? Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select sname, city from S, SP where = and :amount From within a host language, find the names and cities of suppliers supply more than the variable amount quantity part. 嵌入 SQL續(xù) ? The open statement causes the query to be evaluated EXEC SQL open c The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :, :cc Repeated calls to fetch get successive tuples in the query result 嵌入 SQL續(xù) ? A variable called SQLSTATE in the SQL munication area (SQLCA) gets set to ?02021? to indicate no more data is available ? The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL close c Note: above details vary with language. . the Java embedding defines Java iterators to step through result tuples. 游標(biāo)更新 ? Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select * from EMP where city = ?Parise? for update ? To update tuple at the current location of cursor update EMP set SAL = SAL + 100 where current of c 動(dòng)態(tài) SQL ? Allows programs to construct and submit SQL queries at run time. ? The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed. 動(dòng)態(tài) SQL續(xù) ? Example of the use of dynamic SQL from within a C program. char * sqlprog = “update EMP set SAL = SAL *
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號-1