Question
use the "titanic" database and the "titanic3" table.
On April 10, 1912 a well known local Norwegian soothsayer Alfred Motsatte was heard in a Queenstown, Ireland bar rambling in an inebriated stupor and predicting the demise of Her Majesty’s finest ship. “Take ‘ye notice righhhhht here and now,” Motsatte exclaimed as he sloshed his half-empty mug of bar swill, “all women who board the Titanic in first class from Queenstown are doomed to perish in an icy grave and all men who accompany them will survive to become widowers.”
Was he right?
2. Paste the SQL statement you used to determine whether Motsatte was right or wrong.
3. What lifeboat (boat number or letter) contained the most survivors? How many survivors did it contain?
4. Paste the SQL statement you used to determine the lifeboat and number of survivors.
5. How many survivors were in boat 12?
6. Paste the SQL query you used to determine the number of survivors in boat 12.
Explanation / Answer
--/*please use below query to get output*/--
2. Paste the SQL statement you used to determine whether Motsatte was right or wrong.
SELECT COUNT(survived) FROM titanic3 WHERE sex='female' --if you get results greater then 0 the statement is false else true
3. What lifeboat (boat number or letter) contained the most survivors? How many survivors did it contain?
SELECT NewTable.boat,NewTable.survived FROM
(SELECT boat,COUNT(name),SUM(survived) AS survived, AS CountPassenger FROM titanic3
GROUP BY boat) AS NewTable
GROUP BY NewTable.boat HAVING MAX(NewTable.CountPassenger);
4. Paste the SQL statement you used to determine the lifeboat and number of survivors.
SELECT boat,SUM(survived) AS CountPassenger FROM titanic3
GROUP BY boat;
5. How many survivors were in boat 12?
SELECT boat,COUNT(name) AS CountPassenger FROM titanic3 WHERE boat='12'
GROUP BY boat;
6. Paste the SQL query you used to determine the number of survivors in boat 12.
SELECT boat,COUNT(name) AS CountPassenger FROM titanic3 WHERE boat='12'
GROUP BY boat;
--/* below is titanic3 table contain 1310 records which im referring*/--let me if you are using other table
pclass survived name sex age sibsp parch ticket fare cabin embarked boat body home.dest 1 1 Allen, Miss. Elisabeth Walton female 29 0 0 24160 211.3375 B5 S 2 1 1 Allison, Master. Hudson Trevor male 0.9167 1 2 113781 151.5500 C22 C26 S 11 Montreal, PQ / Chesterville, ON 1 0 Allison, Miss. Helen Loraine female 2 1 2 113781 151.5500 C22 C26 S Montreal, PQ / Chesterville, ON 1 0 Allison, Mr. Hudson Joshua Creighton male 30 1 2 113781 151.5500 C22 C26 S 135 Montreal, PQ / Chesterville, ON 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels) female 25 1 2 113781 151.5500 C22 C26 S Montreal, PQ / Chesterville, ON 1 1 Anderson, Mr. Harry male 48 0 0 19952 26.5500 E12 S 3 New York, NY 1 1 Andrews, Miss. Kornelia Theodosia female 63 1 0 13502 77.9583 D7 S 10 Hudson, NY 1 0 Andrews, Mr. Thomas Jr male 39 0 0 112050 0.0000 A36 S Belfast, NI 1 1 Appleton, Mrs. Edward Dale (Charlotte Lamson) female 53 2 0 11769 51.4792 C101 S D Bayside, Queens, NY 1 0 Artagaveytia, Mr. Ramon male 71 0 0 PC 17609 49.5042 C 22 Montevideo, Uruguay 1 0 Astor, Col. John Jacob male 47 1 0 PC 17757 227.5250 C62 C64 C 124 New York, NY 1 1 Astor, Mrs. John Jacob (Madeleine Talmadge Force) female 18 1 0 PC 17757 227.5250 C62 C64 C 4 New York, NY 1 1 Aubart, Mme. Leontine Pauline female 24 0 0 PC 17477 69.3000 B35 C 9 Paris, France 1 1 Barber, Miss. Ellen "Nellie" female 26 0 0 19877 78.8500 S 6 1 1 Barkworth, Mr. Algernon Henry Wilson male 80 0 0 27042 30.0000 A23 S B Hessle, Yorks 1 0 Baumann, Mr. John D male 0 0 PC 17318 25.9250 S New York, NY 1 0 Baxter, Mr. Quigg Edmond male 24 0 1 PC 17558 247.5208 B58 B60 C Montreal, PQ 1 1 Baxter, Mrs. James (Helene DeLaudeniere Chaput) female 50 0 1 PC 17558 247.5208 B58 B60 C 6 Montreal, PQ 1 1 Bazzani, Miss. Albina female 32 0 0 11813 76.2917 D15 C 8 1 0 Beattie, Mr. Thomson male 36 0 0 13050 75.2417 C6 C A Winnipeg, MN 1 1 Beckwith, Mr. Richard Leonard male 37 1 1 11751 52.5542 D35 S 5 New York, NY 1 1 Beckwith, Mrs. Richard Leonard (Sallie Monypeny) female 47 1 1 11751 52.5542 D35 S 5 New York, NY 1 1 Behr, Mr. Karl Howell male 26 0 0 111369 30.0000 C148 C 5 New York, NY 1 1 Bidois, Miss. Rosalie female 42 0 0 PC 17757 227.5250 C 4 1 1 Bird, Miss. Ellen female 29 0 0 PC 17483 221.7792 C97 S 8 1 0 Birnbaum, Mr. Jakob male 25 0 0 13905 26.0000 C 148 San Francisco, CA 1 1 Bishop, Mr. Dickinson H male 25 1 0 11967 91.0792 B49 C 7 Dowagiac, MI 1 1 Bishop, Mrs. Dickinson H (Helen Walton) female 19 1 0 11967 91.0792 B49 C 7 Dowagiac, MI 1 1 Bissette, Miss. Amelia female 35 0 0 PC 17760 135.6333 C99 S 8 1 1 Bjornstrom-Steffansson, Mr. Mauritz Hakan male 28 0 0 110564 26.5500 C52 S D Stockholm, Sweden / Washington, DC 1 0 Blackwell, Mr. Stephen Weart male 45 0 0 113784 35.5000 T S Trenton, NJ 1 1 Blank, Mr. Henry male 40 0 0 112277 31.0000 A31 C 7 Glen Ridge, NJ 1 1 Bonnell, Miss. Caroline female 30 0 0 36928 164.8667 C7 S 8 Youngstown, OH 1 1 Bonnell, Miss. Elizabeth female 58 0 0 113783 26.5500 C103 S 8 Birkdale, England Cleveland, Ohio 1 0 Borebank, Mr. John James male 42 0 0 110489 26.5500 D22 S London / Winnipeg, MB 1 1 Bowen, Miss. Grace Scott female 45 0 0 PC 17608 262.3750 C 4 Cooperstown, NY 1 1 Bowerman, Miss. Elsie Edith female 22 0 1 113505 55.0000 E33 S 6 St Leonards-on-Sea, England Ohio 1 1 Bradley, Mr. George ("George Arthur Brayton") male 0 0 111427 26.5500 S 9 Los Angeles, CA 1 0 Brady, Mr. John Bertram male 41 0 0 113054 30.5000 A21 S Pomeroy, WA 1 0 Brandeis, Mr. Emil male 48 0 0 PC 17591 50.4958 B10 C 208 Omaha, NE
