|
Post by Ja Ona i Pivo on Mar 13, 2009 7:04:04 GMT -5
Need help with my homework Here the table CREATE TABLE Larare ( akronymLarare CHAR(3) PRIMARY KEY, avdelningLarare CHAR(3), namnLarare CHAR(20), lonLarare INT, foddLarare DATETIME ); i need now to create a View to extract the information from TABLE Larare!! This is what ive wrote but it doesnt work create view Personal (Sektionsnamn, Medlemsnamn) as select Avdelning.Namn, Namn.Namn from Larare, avdelningLarare where ; Please fill in if u know
|
|
|
Post by SKORIC on Mar 13, 2009 7:30:12 GMT -5
|
|
|
Post by Ja Ona i Pivo on Mar 13, 2009 7:33:58 GMT -5
lolol
|
|
|
Post by Caslav Klonimirovic on Mar 13, 2009 21:36:48 GMT -5
LOL, I'm tempted to help but it's the weekend.
|
|
|
Post by Ja Ona i Pivo on Mar 14, 2009 10:54:39 GMT -5
LOL, I'm tempted to help but it's the weekend. Come on help me man... Im stuck big time
|
|
|
Post by manijak on Mar 14, 2009 12:37:58 GMT -5
when i was doing my html..i got so annoyed and frustrated..i had to jack-off to calm down before i broke the computer haha
|
|
|
Post by Emperor AAdmin on Mar 14, 2009 22:05:41 GMT -5
I see you are getting some real assistance here
I will post this under announcements and perhaps some members from another forum will be kind enough to assist you.
|
|
|
Post by Sh1 Shonić on Mar 14, 2009 23:34:36 GMT -5
Mislim da ti Pyrros moze pomoci.
|
|
|
Post by Ja Ona i Pivo on Mar 15, 2009 5:08:12 GMT -5
hvala aadmin, hvala shonic!
|
|
Kralj Vatra
Amicus
Warning: Sometimes uses foul language & insults!!!
20%
Posts: 9,814
|
Post by Kralj Vatra on Mar 15, 2009 10:17:17 GMT -5
Need help with my homework Here the table CREATE TABLE Larare ( akronymLarare CHAR(3) PRIMARY KEY, avdelningLarare CHAR(3), namnLarare CHAR(20), lonLarare INT, foddLarare DATETIME ); i need now to create a View to extract the information from TABLE Larare!! This is what ive wrote but it doesnt work create view Personal (Sektionsnamn, Medlemsnamn) as select Avdelning.Namn, Namn.Namn from Larare, avdelningLarare where ; Please fill in if u know MAN!!! where the FUCK is the definition (create clause) of table/view/relation avdelningLarare?
|
|
|
Post by Ja Ona i Pivo on Mar 15, 2009 11:34:20 GMT -5
man i have no idea. this is my first assigment.. i have no clue.. Heres the whole query.
Number 10 is where i should create a view, i must use the first table info to make it..
-- 1 Skapa databas
CREATE DATABASE uppgift2;
USE uppgift2;
-- 2 Skapa tabellen lärare
CREATE TABLE Larare ( akronymLarare CHAR(3) PRIMARY KEY, avdelningLarare CHAR(3), namnLarare CHAR(20), lonLarare INT, foddLarare DATETIME );
DROP TABLE Larare;
SELECT * FROM Larare;
-- 3 Lägg in 10 lärare
INSERT INTO Larare VALUES ('MOS', 'APS', 'Mikael', 15000, '1968-03-07'); INSERT INTO Larare VALUES ('MOL', 'AIS', 'Mats-Ola', 15000, '1978-12-07'); INSERT INTO Larare VALUES ('AJA', 'APS', 'Andreas', 15000, '1988-08-07'); INSERT INTO Larare VALUES ('CJH', 'APS', 'Conny', 15000, '1943-01-09'); INSERT INTO Larare VALUES ('CSA', 'APS', 'Charlie', 15000, '1969-04-07'); INSERT INTO Larare VALUES ('BHR', 'AIS', 'Birgitta', 15000, '1964-02-07'); INSERT INTO Larare VALUES ('MAP', 'APS', 'Marie', 15000, '1972-06-07'); INSERT INTO Larare VALUES ('LRA', 'APS', 'Linda', 15000, '1975-03-07'); INSERT INTO Larare VALUES ('ACA', 'APS', 'Anders', 15000, '1967-09-07'); INSERT INTO Larare VALUES ('BBE', 'APS', 'Betty', 15000, '1968-07-07');
SELECT * FROM Larare;
-- 4 Radera med DELETE
DELETE FROM Larare WHERE namnLarare = 'Mikael'; DELETE FROM Larare WHERE avdelningLarare = 'AIS'; DELETE FROM Larare LIMIT 2; DELETE FROM Larare;
-- 5 Ändra i befintlig tabell med ALTER TABLE
ALTER TABLE Larare ADD COLUMN kompetensLarare INT; ALTER TABLE Larare DROP COLUMN kompetensLarare; ALTER TABLE Larare ADD COLUMN kompetensLarare INT DEFAULT 5 NOT NULL;
SELECT * FROM Larare;
-- 6 Välj ut och presentera innehållet i tabellen lärare
-- Where
SELECT * FROM Larare WHERE avdelningLarare = 'AIS'; SELECT * FROM Larare WHERE akronymLarare LIKE 'M%'; SELECT * FROM Larare WHERE namnLarare LIKE 'M%'; SELECT * FROM Larare WHERE lonLarare LIKE '=>20000%'; SELECT * FROM Larare WHERE namnLarare LIKE 'M%' AND '=>20000';
-- order by
SELECT namnLarare, lonLarare from Larare; SELECT namnLarare FROM Larare ORDER BY namnLarare ASC; SELECT namnLarare FROM Larare ORDER BY namnLarare DESC; SELECT lonLarare, namnLarare FROM Larare ORDER BY lonLarare ASC; SELECT lonLarare, namnLarare FROM Larare ORDER BY lonLarare DESC;
-- Alias
ALTER TABLE Larare CHANGE namnLarare namn CHAR(30); ALTER TABLE Larare CHANGE lonLarare lön INT; ALTER TABLE Larare CHANGE avdelningLarare Avdelning CHAR(3);
SELECT * FROM Larare;
-- 7 Använd group by och beräknande funktioner
SELECT SUM(namn) FROM Larare GROUP BY Avdelning ASC;
SELECT COUNT(lön) FROM Larare GROUP BY Avdelning ASC;
SELECT AVG(lön) FROM Larare GROUP BY Avdelning;
SELECT * FROM Larare;
-- 8 Ändra värden med Uppdate
UPDATE Larare SET lön = 17500 * 0.1 WHERE namn = 'Mikael'; UPDATE Larare SET lön = 17500 + 1750 WHERE namn = 'Mikael';
UPDATE Larare SET lön = 15800 * 0.1 WHERE namn = 'Mats-Ola'; UPDATE Larare SET lön = 15800 + 1580 WHERE namn = 'Mats-Ola';
UPDATE Larare SET lön = 13800 * 0.1 WHERE namn = 'Andreas'; UPDATE Larare SET lön = 13800 + 1380 WHERE namn = 'Andreas';
SELECT * FROM Larare;
-- 9 Inbyggda funktioner
SELECT CURDATE();
SELECT namn, foddLarare, CURDATE() FROM Larare;
SELECT namn, foddLarare, CURDATE(), (YEAR(CURDATE())-YEAR(foddLarare)) - (RIGHT(CURDATE(),5)<RIGHT(foddLarare,5))AS År FROM Larare GROUP BY År DESC;
-- 10 Vyer förenklar
create view Personal as select Personal.Namn, Personal.Namn from Personal, Larare where Ledare = Medlemsnummer;
create view Personal (Sektionsnamn, Medlemsnamn)as select Avdelning.Namn, Namn.Namn from Larare, avdelningLarare where Ledare = Medlemsnummer;
DROP VIEW Personal;
SELECT * FROM Larare;
|
|
Kralj Vatra
Amicus
Warning: Sometimes uses foul language & insults!!!
20%
Posts: 9,814
|
Post by Kralj Vatra on Mar 15, 2009 12:39:59 GMT -5
"CREATE TABLE Larare ( akronymLarare CHAR(3) PRIMARY KEY, avdelningLarare CHAR(3), namnLarare CHAR(20), lonLarare INT, foddLarare DATETIME );
DROP TABLE Larare;
SELECT * FROM Larare;"
is wrong drop table means DROP the definition of the table and its data.
"create view Personal as select Personal.Namn, Personal.Namn from Personal, Larare where Ledare = Medlemsnummer;"
i never knew there is recursive views definitions... thats insane... thats equivalent of saying that view Personal should be defined as selecting smth from view personal which is defined as selecitng smth from view personal .... etc.... which never ends...
Your swedish professor is desperately in need of SQL lessons.
Also it could be that MySQL sucks...
man wanna learn true databases? Want a nice job with Oracle? then go learn PostgreSQL, which IMO is the finest one OS database can get. Personally its the ODBMS/RDBMS of professional choice.
|
|
Kralj Vatra
Amicus
Warning: Sometimes uses foul language & insults!!!
20%
Posts: 9,814
|
Post by Kralj Vatra on Mar 15, 2009 15:07:59 GMT -5
An introductory found here: www.sql-tutorial.net/SQL-WHERE.aspcould be used as a first source of info... really man, what class/course is this? SQL? on which year? which university?
|
|
|
Post by bob1389 on Mar 15, 2009 18:53:20 GMT -5
Next week it's my turn for homework help. ;D
|
|
|
Post by Emperor AAdmin on Mar 15, 2009 19:27:51 GMT -5
ok
|
|
|
Post by Arxileas on Mar 15, 2009 22:12:15 GMT -5
Strange SQL commands there............
|
|
|
Post by Ja Ona i Pivo on Mar 16, 2009 4:05:43 GMT -5
An introductory found here: www.sql-tutorial.net/SQL-WHERE.aspcould be used as a first source of info... really man, what class/course is this? SQL? on which year? which university? It's third year, but its the first Database course, so it really doesnt matter what year... All the way from top to the bottom the code works really fine. There is no problem with it what so ever. Its just the last thing i can get working and its the VIEW..
|
|
Kralj Vatra
Amicus
Warning: Sometimes uses foul language & insults!!!
20%
Posts: 9,814
|
Post by Kralj Vatra on Mar 16, 2009 4:55:21 GMT -5
An introductory found here: www.sql-tutorial.net/SQL-WHERE.aspcould be used as a first source of info... really man, what class/course is this? SQL? on which year? which university? It's third year, but its the first Database course, so it really doesnt matter what year... All the way from top to the bottom the code works really fine. There is no problem with it what so ever. Its just the last thing i can get working and its the VIEW.. Man, 1) subscribe to the proper MySQL or just SQL rdbms-whatever mailing list/forum 2) read what your professor has supplied to you or your book 3) there is no chance on earth that: CREATE TABLE Larare ( akronymLarare CHAR(3) PRIMARY KEY, avdelningLarare CHAR(3), namnLarare CHAR(20), lonLarare INT, foddLarare DATETIME );
DROP TABLE Larare;
SELECT * FROM Larare;
can work, since DROP TABLE Larare; destroys the table and the next command: SELECT * FROM Larare; should return smth like "ERROR: no such table" now, generally, the WHERE clause in a view is just a normal WHERE clause in ANY SQL SELECT statement! Views are not materialized, so they are essentially a predefined SQL select statement! So that leads to the question DO YOU KNOW SQL SELECT? Do you know what JOIN is? Natural JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CARTESIAN PRODUCT JOIN, etc.... Have you read all this staff? Read your book on relational theory and MySQL can wait for some time... Sorry man, but posting a technical question about MySQL in a SERB-ethnic forum wont help, especially if you haven't done your homework! Now go and read your book!
|
|
|
Post by Ja Ona i Pivo on Mar 16, 2009 6:40:30 GMT -5
It's third year, but its the first Database course, so it really doesnt matter what year... All the way from top to the bottom the code works really fine. There is no problem with it what so ever. Its just the last thing i can get working and its the VIEW.. Man, 1) subscribe to the proper MySQL or just SQL rdbms-whatever mailing list/forum 2) read what your professor has supplied to you or your book 3) there is no chance on earth that: CREATE TABLE Larare ( akronymLarare CHAR(3) PRIMARY KEY, avdelningLarare CHAR(3), namnLarare CHAR(20), lonLarare INT, foddLarare DATETIME );
DROP TABLE Larare;
SELECT * FROM Larare;
can work, since DROP TABLE Larare; destroys the table and the next command: SELECT * FROM Larare; should return smth like "ERROR: no such table" now, generally, the WHERE clause in a view is just a normal WHERE clause in ANY SQL SELECT statement! Views are not materialized, so they are essentially a predefined SQL select statement! So that leads to the question DO YOU KNOW SQL SELECT? Do you know what JOIN is? Natural JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CARTESIAN PRODUCT JOIN, etc.... Have you read all this staff? Read your book on relational theory and MySQL can wait for some time... Sorry man, but posting a technical question about MySQL in a SERB-ethnic forum wont help, especially if you haven't done your homework! Now go and read your book! i know what u mean.. The Drop Table is there just for the teacher to se that i know how to drop the table, i dont need to use the code.. I can just create the table then SELECT * FROM.. I dont need to use all the code.. The code works fine. MySQL validates it right.. My only problem is creating the view.. <-- Thnx for help anyway.
|
|
Kralj Vatra
Amicus
Warning: Sometimes uses foul language & insults!!!
20%
Posts: 9,814
|
Post by Kralj Vatra on Mar 16, 2009 7:38:32 GMT -5
alright explain in english what you want this view to represent.
There is no way on earth e.g. for me to write down *YOUR* WHERE clause because you KNOW what this view is about....
executing the same command in PostgreSQL i get:
postgres@2rm=# create view Personal as select Personal.Namn, Personal.Namn from Personal, Larare where Ledare = Medlemsnummer; ERROR: relation "personal" does not exist postgres@2rm=#
Because you CANT use the definition of the view in the FROM clause of this very view definition, thus a recursive definition.
It wont work in PostgreSQL, Oracle, Sybase, SQL-Server, etc...
Please tell your professor that this CREATE VIEW statement is 100% wrong.
|
|